SQL テーブル参照先の条件句 JOIN vs EXISTS

問題設定

環境は例によってOracle DB 12cのSCOTTスキーマのemp表とdept表を用いる。
データやテーブル構成は前記と同じなのでこちらを参照。
totech.hateblo.jp

今回は、「給与が3000以上である従業員が所属している部署の部署番号と部署名を一覧化する」SQLを考える。
ここで、「最終的にに取得したいデータは全てdept表にあり、emp表は絞込みのためにしか使わない」という点が重要。

以下のようなSQLを実行する。

select EMPNO, ENAME, SAL, DEPTNO
from SCOTT.EMP e
where sal >= 3000
order by e.deptno

このような結果が得られるので、部署番号10と20の部署番号、部署名が正解の結果となる。

パターン1: 結合を使う方法

比較的分かりやすいのはこのパターン。あまり考えずに書く場合はだいたいこうなる気がする。

select distinct
 d.deptno
, d.dname
from
 scott.emp e
, scott.dept d
where e.deptno = d.deptno
and e.sal >= 3000

JOIN句は使っていないが、emp表とdept表を結合してwhere句で絞り込んでいる。
見た目には分かりやすい。
emp表と結合してしまうと重複が発生してしまうため、最後にDISTINCTしているところがネックとなる。
実行結果は以下のようになり、正しい結果となっているよう。

実行計画を見るとHASH UNIQUEによって重複削除を行っている。

----------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |   |  3 | 144 |  6 (34)| 00:00:01 |
| 1 | HASH UNIQUE    |   |  3 | 144 |  6 (34)| 00:00:01 |
|* 2 | HASH JOIN    |   |  3 | 144 |  5 (20)| 00:00:01 |
|* 3 |  TABLE ACCESS FULL| EMP |  3 |  78 |  2 (0)| 00:00:01 |
| 4 |  TABLE ACCESS FULL| DEPT |  4 |  88 |  2 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - access("E"."DEPTNO"="D"."DEPTNO")
 3 - filter("E"."SAL">=3000)

パターン2: exists句を使う方法

結合を使わずにexsits句で表現することもできる。
その場合のSQLはこのようになる。

select
 d.deptno
, d.dname
from
scott.dept d
where exists
 (select 1
 from scott.emp e
 where e.deptno = d.deptno
  and e.sal >= 3000)

from句には実際に値を取得したいdept表だけが残り、
where句の中のサブクエリとしてemp表を使っている。
見た目はサブクエリを使うせいでやや複雑になっているが、exsits句だと重複は発生しないのでDISTINCTをする必要はなくなった。
実行結果は以下のようになり、先ほどと同じ結果となった（順番は変わったが）。

実行計画を見ておく。HASH JOIN SEMIによる結合が行われ、代わりに重複削除処理はなくなった。

---------------------------------------------------------------------------
| Id | Operation     | Name | Rows | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |   |  3 | 144 |  5 (20)| 00:00:01 |
|* 1 | HASH JOIN SEMI  |   |  3 | 144 |  5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT |  4 |  88 |  2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP |  3 |  78 |  2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - access("E"."DEPTNO"="D"."DEPTNO")
 3 - filter("E"."SAL">=3000)

まとめ(どちらがよいか)

個人的にはパターン2のexistsを使うほうが、
余計な結合と重複削除がないため、パターン1の結合を使うケースより早くなるケースが多いような気がする。
ただし前回の記事ほど一般的な結果とは言えないように思うので、
このような取得列がないテーブルを条件句で参照する場合は、結合ではなくexistsによる選択肢があることを忘れないようにしたい。

以上。