INとEXISTS、NOT INとNOT EXISTSの違い

Question

EXISTSやNOT EXISTSを使用するケースはどのような時でしょうか？
例えば、以下のようなクエリを投げた時に同じような結果となるので、使い分けがわかりません。

SELECT *
FROM customers
WHERE EXISTS (
    SELECT *
    FROM orders
    WHERE orders.customer_id = customers.id
)

SELECT *
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
)

こういう場合はIN（NOT IN）だけどこういう場合はEXISTS（NOT EXISTS）を使うという判断基準が欲しいです。
よろしくお願いいたします！

Answer 1

他の方の回答があるので、リンクだけ。
INとEXISTSの違い

INとEXISTSは等価の結果を返すことが可能ですが意味は違います。処理から考えても良いし、どちらのインデックスを使いたいかで考えても良いけれど、意味を考えないで決め打ちはやめましょう。意味を考えないで書かれた長いSQLは、何がしたいのか分からないし、全く読めないものになっています。

Comments

[maisumakun]

このリンクした記事には、自分は懐疑的です。

SQL文の「意味」を人間にわかりやすく書いたところで、実行計画が適切にならないのであれば、時間の浪費となってしまいます。

[sazi]

コメントありがとうございます。
リンクで書かれている内容は、例えば、仕様に則っていない記述に合わせたインデックスを適用して、実行計画を最適にするようなアプローチにならないように注意することと解釈しています。
最終的にたどり着く結果が同じだとしても、道筋を立てて進むのであれば、次に生かせるものとして、その時間は浪費にはならないと考えます。

[sazi]

回答は、このリンク推しということではなく、取捨選択する為の情報は多いほうが良いだろうというのが意図です。

[sazi]

「INとEXISTSは等価」と書いてあり、三値のケースは触れてないのはどうかな？と思ったりはしています。

Answer 2

まず、テーブルを使わない、id IN (1, 9, 27, 63)のような条件については、EXISTSで表現するほうが手間です。

あと、テーブルと関連するINとEXISTSは比較的容易に変換できますが、NOT INは、テーブルから抽出した値に1つでもNULLが入っていると、三値論理によって結果がUNKNOWNとなり、1件もヒットしない事態となるので要注意です（Qiita）。

Answer 3

「INとEXISTS」でネット検索すると、いろんな記事が見つかるのですが、どのくらいチェックされていますか？
それぞれ、SQLの実行計画を取得すると、結果的に同じに見えても内部で処理コストのかかり方が違うことが見えてくると思います。

以下、参考記事。
INとEXISTSの実行計画の違い
2つの副問い合わせの違い - オラクル・Oracleをマスターするための基本と仕組み
『NOT IN』『NOT EXISTS』の検証 | WOWブログ

案外、今どきのネット検索は、疑問を素直に入力すると、
それっぽい情報が出てくるようになっているものですよ。