サブクエリのIN句とEXISTS句ってどう違うの？

Daily Blogging79日目

IN句が使われているscopeがあって、そいつが遅い遅い
IN句じゃなくてEXISTS句の方が速いよとは聞くけどなんでだろうか

これを例に考える
※postgreSQL

### customers テーブル

| カラム名      | 説明           |
|--------------|--------------|
| customer_id  | 顧客の一意なID |
| customer_name | 顧客の名前     |
| email        | メールアドレス |
| created_at   | 作成日時       |

---

### orders テーブル

| カラム名      | 説明           |
|--------------|--------------|
| order_id     | 注文の一意なID |
| customer_id  | 顧客のID       |
| order_date   | 注文日         |
| total_amount | 注文合計金額   |

IN句とEXISTS句の違い

EXISTS：データがあるかないかだけを確認
IN：サブクエリの結果の中から一致するデータあるかを確認

もうちょっと詳しくみていく

IN句の挙動

こういうサブクエリにIN句があるケース

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= '2025-03-01'
);

IN句の場合、サブクエリの評価が先に行われる

# INの中のこのクエリ
SELECT customer_id
    FROM orders
    WHERE order_date >= '2025-03-01'

サブクエリの結果はメモリにロードされる。
その後、主テーブルのクエリが実行され、主テーブルの行ごとにサブクエリの結果と比較して該当する値があるかを確認している。

EXISTS句の挙動

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2025-03-01'
);

IN句とは評価順が異なり、まず主テーブルのクエリが実行される。
この時、主テーブルの行ごとにEXISTが実行され、条件にあるレコードが1つでもあるか確認する。
1つでもレコードがあればすぐにtrueを返しその行の処理が終了する。

つまりこういうこと

IN句が遅いと言われている理由はこう

• メモリを使用して処理が実行される
  • サブクエリの結果が多ければ多いほどメモリを喰う
• 主テーブルの行ごとにサブクエリの値に検索をかける

特にサブクエリのサイズが大きいほど、IN句のパフォーマンスは劣化していく

それに対しEXISTS句は、

• サブクエリの結果をメモリにロードしない
• 条件にあるレコードが一つでもあればすぐに処理をやめる
• サブクエリのサイズにそこまで影響されない

だからEXISTS句の方が速いし、そっち使った方がいいねって言われているんだと思われる。

IN句を使うタイミング

じゃあIN句っていらんくねってなるけど一応メリットもある

• 可読性が高い

IN句の方が、パッとみたときに何をしているクエリなのかがわかりやすい。
単純にクエリがシンプルになる。

サブクエリの結果が小さい場合はIN句でもパフォーマンスがそこまで落ちないらしいので
IN句で問題なければIN句にしておいた方がいいかも

サブクエリのサイズがデカすぎると...

サブクエリのサイズがあまりにも大きいと、EXISTS句でもIN句でも同じ実行計画になることがある。
今回改善しようと思ったscopeも、IN句でもEXISTS句でも同じ実行結果になった

EXISTS句使ってるからOKでしょ！とはならずに、ちゃんと実行計画を確認しよう

ちなみにサブクエリのサイズが小さい時にパフォーマンスに違いがあるかは検証していない。
ネットで調べてると、いや実行計画同じになりますねって言ってる人結構いる。