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

昨日の記事に続き、今日もSQLネタ。

さて、あるテーブルにある行を検索する際に検索条件が外部キーで参照した別テーブルの列にあり、かつそのテーブルの値をselectしない場合、
そのようなSQLは結合もしくはexists句を使う2パターンで表現できる。

今回はこの2パターンを検証する。

問題設定

環境は例によって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の部署番号、部署名が正解の結果となる。

EMPNO	ENAME	SAL	DEPTNO
7839	KING	5000	10
7902	FORD	3000	20
7788	SCOTT	3000	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しているところがネックとなる。
実行結果は以下のようになり、正しい結果となっているよう。

DEPTNO	DNAME
10	ACCOUNTING
20	RESEARCH

実行計画を見ると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をする必要はなくなった。
実行結果は以下のようになり、先ほどと同じ結果となった（順番は変わったが）。

DEPTNO	DNAME
20	RESEARCH
10	ACCOUNTING

実行計画を見ておく。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による選択肢があることを忘れないようにしたい。

以上。