【SQL】EXISTS, IN, JOINの違い

EXISTS句をIN, JOINで代用する

SQLのEXISTS, NOT EXISTSによるデータ抽出はIN演算子や、JOINによる結合を使った場合でも同じ結果を求めることが出来ます。

EXISTSをINまたはJOINで代用した場合のSQLの記述と、使い分ける理由などを解説します。

EXISTS句の基本は下記記事にて解説しています。

【SQL】EXISTS, NOT EXISTSによるデータ抽出

IN演算子で代用

IN演算子（IN句）
SELECT col1, col2 FROM table WHERE カラム名 IN（リスト）

WHERE句で指定したカラムの値がINの引数のリストに含まれることを検索条件にする

EXISTSをINで代用

（EXISTS）生徒表から文化部所属の生徒を全て抽出

SELECT student_code 生徒番号, student_name 名前, club_code 所属部活コード FROM student 生徒
WHERE EXISTS (SELECT * FROM club 部活
                WHERE 部活.club_code = 生徒.club_code AND club_type = '文化部')

INで書き換える

SELECT student_code 生徒番号, student_name 名前, club_code 所属部活コード FROM student 
WHERE club_code IN (SELECT club_code FROM club WHERE club_type = '文化部')

INの後の括弧の副問合せによって種別が文化部である部活コード（club_code）のリストが返され、主問合せの生徒表からリストに含まれる所属部活コードをもつ行を抽出します。

INの後ろの結果は以下と同じになります。
SELECT student_code 生徒番号, student_name 名前, club_code 所属部活コード FROM student WHERE club_code IN (‘C_001′,’C_002′,’C_003’)

NOT EXISTSをNOT INで代用

（NOT EXISTS） 部活表から所属する生徒がいない部を抽出

SELECT club_code 部活コード, club_name 名前, club_type 種別 FROM club
WHERE NOT EXISTS (SELECT * FROM student 
                    WHERE student.club_code = club.club_code)

NOT INで書き換える

SELECT club_code 部活コード, club_name 名前, club_type 種別 FROM club 
WHERE club_code NOT IN (SELECT club_code FROM student WHERE club_code IS NOT NULL)

リストのNULLに注意
副問合せで返される結果のリストにNULLが含まれる場合は、NOT INが期待通りの結果となりません。
上記のSQLのように副問合せの条件でNULLを省いた状態にする必要があります。

INとEXISTSはどちらを使う?

	パフォーマンス	可読性
EXISTS	○	△
IN	△	○

前述のとおりEXISTS, NOT EXISTSはそれぞれIN, NOT INで同様の結果を得ることが出来ますが、INの引数としてリストではなく副問合せを用いる場合は一般的にEXISTSの方を使用することが推奨されているようです。

これはINの場合、主問い合わせよりも先に実行される副問合せによって一時的に作成される中間テーブルの全件参照でパフォーマンスに負荷がかかる点があることに加え、
一方のEXISTSは、相関サブクエリの結合条件としているカラムにインデックスが張られている場合、副問合せ側の実表を参照せずインデックスのみの参照で済むことと、副問合せの中から条件に合致する行が存在した時点で検索を打ち切り、副問合せの結果を全表検索する必要がないためパフォーマンス的にINよりも優れていると言われているためです。

しかし、可読性においてはEXISTSよりもIN句を用いた方が意味がわかりやすい記述となりますので、採用するRDBMSのバージョンや性能、機能の進歩などによりINの使用で応答時間などのパフォーマンスが問題なければ、ケースバイケースとも言えるようです。

結合（JOIN）で代用

EXISTS, NOT EXISTSをJOINによる結合で代用します。

EXISTSをJOINで代用

（再掲: EXISTS）生徒表から文化部所属の生徒を全て抽出

SELECT student_code 生徒番号, student_name 名前, club_code 所属部活コード FROM student 生徒
WHERE EXISTS (SELECT * FROM club 部活
                WHERE 部活.club_code = 生徒.club_code AND club_type = '文化部')

JOINで書き換える

SELECT 生徒.student_code 生徒番号, 生徒.student_name 名前, 生徒.club_code 所属部活コード 
FROM student 生徒 INNER JOIN club 部活 ON 生徒.club_code = 部活.club_code WHERE 部活.club_type = '文化部'

EXISTSの相関サブクエリで行っている表の結合を、INNER JOINによる生徒表と部活表の内部結合で代用できます。生徒表にとって結合先である部活表で、検索条件の種別「文化部」を指定することでEXISTSで行っていたことと同じ結果を抽出できます。

NOT EXISTSをJOINで代用

（再掲: NOT EXISTS） 部活表から所属する生徒がいない部を抽出

SELECT club_code 部活コード, club_name 名前, club_type 種別 FROM club
WHERE NOT EXISTS (SELECT * FROM student 
                    WHERE student.club_code = club.club_code)

JOINで書き換える

SELECT 部活.club_code 部活コード, 部活.club_name 名前, 部活.club_type 種別 FROM club 部活 
LEFT OUTER JOIN student 生徒 ON 部活.club_code = 生徒.club_code 
WHERE 生徒.student_name IS NULL

部活表と生徒表をLEFT OUTER JOINで外部結合することで部活表の行を全て取り出し、生徒のデータがNULLである行を絞り込めば、NOT EXISTSの「存在しないこと」と同じ結果を抽出できます。

（外部結合イメージ）

JOINとEXISTSはどちらを使う？

JOINもEXISTSと同じ結果を求められることがわかりました。

JOINもEXISTS同様、結合キーにインデックスを利用できるため、その場合は前述のINよりもパフォーマンス的には優れるようです。

しかし、JOINの際にパフォーマンスに影響があるソート（並べ換え）の処理が発生するケースではインデックスが有効なEXISTSを選択する方が良いようです。

例として、以下のようにJOINを使った検索結果から重複を省くケースがあります。

（例）部活表から所属する生徒が存在する部を抽出

SELECT club_name 部名 FROM club 部活 INNER JOIN student 生徒 ON 部活.club_code = 生徒.club_code

（結果）

+-----------------+
| 部名            |
+-----------------+
| 吹奏楽部        |
| 吹奏楽部        |
| 美術部          |
| サッカー部      |
| 野球部          |
| 野球部          |
| 水泳部          |
+-----------------+

上記から重複を削除

SELECT DISTINCT club_name 部名 FROM club 部活 INNER JOIN student 生徒 ON 部活.club_code = 生徒.club_code

（結果）

+-----------------+
| 部名            |
+-----------------+
| 吹奏楽部        |
| 美術部          |
| サッカー部      |
| 野球部          |
| 水泳部          |
+-----------------+

重複の削除にDISTINCTを使用していますが、DISTINCTもDB内部でソートの処理が行われるようです。

前述したとおりソートの処理はパフォーマンスに影響することがありますが、代わりにEXISTSを使用することでそれを回避できます。

DISTINCTと同じ結果を求めるEXISTS

SELECT club_name 部名 FROM club 部活 
WHERE EXISTS (SELECT * FROM student 生徒
                    WHERE 生徒.club_code = 部活.club_code)

まとめ

EXISTS, IN, JOINそれぞれ、パフォーマンス面だけでみた場合、

（優）EXISTS > JOIN > IN（劣）

上記が一般的な順位付けですが、構築するシステムの規模や、RDBMSの性能、パフォーマンスと可読性のバランスなど、総合的な判断においての選択となる場合もあるようです。