SQLのEXISTSとINの違い｜NULLの罠と実行計画で見る使い分け

EXISTSとINの違いとは、「サブクエリが1行でも返すか（EXISTS）」で判定するか、「値がサブクエリの結果リストに含まれるか（IN）」で判定するかの違いです。 肯定形（IN / EXISTS）では、現在のPostgreSQLとMySQLはどちらもほぼ同じ実行計画に書き換えるため、速度差はほとんど出ません。差がはっきり出るのは否定形で、NOT IN はサブクエリにNULLが1つでも混ざると結果が0件になり、条件によっては実行計画も大きく変わります。

この記事では、PostgreSQL 16.13 と MySQL 8.0.46 に同じ10万件（会員）＋20万件（注文）のデータを入れ、IN / EXISTS / JOIN / NOT IN / NOT EXISTS / LEFT JOIN IS NULL の6通りを EXPLAIN ANALYZE の実行結果つき で比較しました。「どちらが速いか」を言い伝えではなく実行計画で確かめ、NOT IN が 2ms → 33秒になる条件、NULLで0件になる再現SQLと直し方、迷ったときの判断表までまとめています。

EXISTSとINの違いの早見表

迷ったら「肯定形は読みやすい方、否定形は NOT EXISTS」 と覚えておけば、結果の誤りも性能の大きな差も避けられます。

サブクエリの種類を早見表で整理する

EXISTSとINの話に入る前に、サブクエリ（SQLの中に書くSELECT文）の種類を整理します。INとEXISTSは「表サブクエリ」を条件に使う書き方 です。

「相関」は 外側のクエリの列（m.id など）をサブクエリの中で参照している という意味です。論理的には外側の1行ごとにサブクエリを評価しますが、実際にはオプティマイザが結合に書き換えることが多く、「相関サブクエリ＝遅い」とは限りません（後の実測で確認します）。

スカラーサブクエリが2行以上を返したときのエラーは次のとおりです（後述のサンプルデータで実行）。

-- PostgreSQL 16
ERROR:  more than one row returned by a subquery used as an expression
-- MySQL 8.0
ERROR 1242 (21000) at line 1: Subquery returns more than 1 row

IN の左右で列数が合わないときは、PostgreSQL では ERROR: subquery has too many columns になります。

検証に使ったテーブルとデータ（コピペ用）

小さなデータで動きを確かめ、大きなデータで実行計画を比べます。まずは結果が目で追える4件のデータです。PostgreSQL・MySQL どちらでもそのまま動きます。

CREATE TABLE d_members (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE d_orders  (id INT PRIMARY KEY, member_id INT, amount INT);

INSERT INTO d_members VALUES (1,'佐藤'),(2,'鈴木'),(3,'高橋'),(4,'田中');
-- 注文104 は会員が未設定（member_id が NULL）
INSERT INTO d_orders VALUES (101,1,1200),(102,1,800),(103,2,3000),(104,NULL,500);

性能比較用は、会員10万件・注文20万件です（注文のある会員は73,381人、注文のない会員は26,619人）。PostgreSQL 16 では次のSQLで作りました。

CREATE TABLE members (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders  (id int PRIMARY KEY, member_id int, amount int NOT NULL);
INSERT INTO members SELECT g, 'member' || g FROM generate_series(1,100000) g;
-- 会員1〜80000の中からハッシュで散らして注文を作る
INSERT INTO orders
SELECT g, (hashint4(g) & 2147483647) % 80000 + 1, (g % 50) * 100
FROM generate_series(1,200000) g;
CREATE INDEX idx_orders_member ON orders(member_id);
VACUUM ANALYZE members;
VACUUM ANALYZE orders;

MySQL 8.0 には同じ構造のテーブル（orders に KEY idx_orders_member (member_id)）を作り、注文データは PostgreSQL から \copy orders TO 'orders.csv' CSV で書き出したCSVを LOAD DATA LOCAL INFILE で読み込んで、両DBでまったく同じ行 にそろえています。

INとEXISTSの基本の書き方と結果

「注文したことのある会員」を3通りで取り出します。

-- IN：member_id のリストに含まれるか
SELECT m.id, m.name FROM d_members m
WHERE m.id IN (SELECT o.member_id FROM d_orders o)
ORDER BY m.id;

-- EXISTS：その会員の注文が1件でもあるか（相関サブクエリ）
SELECT m.id, m.name FROM d_members m
WHERE EXISTS (SELECT 1 FROM d_orders o WHERE o.member_id = m.id)
ORDER BY m.id;

実行結果（PostgreSQL 16、どちらも同じ）:

 id | name 
----+------
  1 | 佐藤
  2 | 鈴木
(2 rows)

EXISTS のサブクエリのSELECT句は評価されません。 SELECT 1、SELECT *、SELECT NULL のどれでも結果は同じなので、意図が伝わりやすい SELECT 1 がよく使われます。

JOINとの違いは「重複の出方」

同じことを JOIN で書くと、注文の件数ぶん会員が重複します。

SELECT m.id, m.name FROM d_members m
JOIN d_orders o ON o.member_id = m.id
ORDER BY m.id;

 id | name 
----+------
  1 | 佐藤
  1 | 佐藤
  2 | 鈴木
(3 rows)

佐藤さんは注文が2件あるため2行になります。「存在するかどうか」だけを知りたいときは IN / EXISTS、子テーブルの列も一緒に取りたいときは JOIN と使い分けるのが基本です。JOIN で重複を消すには DISTINCT が必要で、その分の並べ替えや集約が実行計画に加わります。JOIN の種類そのものについては、別記事「SQLのJOINの違い」で詳しく扱っています。

NOT INにNULLが混ざると0件になる（再現と直し方）

ここがEXISTSとINの違いで いちばん結果に影響する点 です。「注文したことのない会員」を取り出します。期待する答えは高橋さんと田中さんの2人です。

-- 直す前：NOT IN
SELECT id, name FROM d_members
WHERE id NOT IN (SELECT member_id FROM d_orders);

実行結果（PostgreSQL 16）:

 id | name 
----+------
(0 rows)

MySQL 8.0 でも同じく COUNT(*) が 0 になりました。

なぜ0件になるのか

id NOT IN (1, 1, 2, NULL) は id <> 1 AND id <> 1 AND id <> 2 AND id <> NULL と同じ意味です。id <> NULL は TRUE でも FALSE でもなく UNKNOWN（不明） になるため、AND 全体が TRUE になれず、WHERE句を通る行がなくなります。

SELECT 3 NOT IN (1,2,NULL) AS r1, 3 IN (1,2,NULL) AS r2, 1 IN (1,2,NULL) AS r3;

 r1 | r2 | r3 
----+----+----
    |    | t
(1 row)

r1 と r2 は空欄＝NULL（UNKNOWN）です。肯定形の IN は「一致するものがあれば TRUE」なので NULL の影響を受けにくい一方、NOT IN は「どれとも一致しない」と言い切れないため結果が消えます。 MySQL でも SELECT 3 NOT IN (1,2,NULL) は NULL を返しました。

直し方は3通り

-- 直し方1：NOT EXISTS（おすすめ）
SELECT id, name FROM d_members m
WHERE NOT EXISTS (SELECT 1 FROM d_orders o WHERE o.member_id = m.id);

-- 直し方2：サブクエリから NULL を除く
SELECT id, name FROM d_members
WHERE id NOT IN (SELECT member_id FROM d_orders WHERE member_id IS NOT NULL);

-- 直し方3：LEFT JOIN … IS NULL
SELECT m.id, m.name FROM d_members m
LEFT JOIN d_orders o ON o.member_id = m.id
WHERE o.id IS NULL;

実行結果（直し方1、PostgreSQL 16）:

 id | name 
----+------
  4 | 田中
  3 | 高橋
(2 rows)

直し方2も高橋・田中の2行を返しました。行の順番は ORDER BY を書かない限り保証されない ので、並びを固定したいときは ORDER BY id を付けてください。

LEFT JOIN 版で WHERE o.member_id IS NULL のように NULL を取りうる列で判定すると、意図と違う行が混ざることがあります。右表の主キーで判定する と安全です。NULL全般の扱いは別記事「SQLのNULLの扱い」にまとめています。

実行計画で比べる：10万件での6パターン実測

ここからは会員10万件・注文20万件のテーブルで、6通りの書き方を EXPLAIN ANALYZE で比べます。どれも件数を数える SELECT count(*) … の形にそろえました。

-- 1. IN
SELECT count(*) FROM members m WHERE m.id IN (SELECT o.member_id FROM orders o);
-- 2. EXISTS
SELECT count(*) FROM members m WHERE EXISTS (SELECT 1 FROM orders o WHERE o.member_id = m.id);
-- 3. JOIN + DISTINCT
SELECT count(DISTINCT m.id) FROM members m JOIN orders o ON o.member_id = m.id;
-- 4. NOT IN
SELECT count(*) FROM members m WHERE m.id NOT IN (SELECT o.member_id FROM orders o);
-- 5. NOT EXISTS
SELECT count(*) FROM members m WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.member_id = m.id);
-- 6. LEFT JOIN IS NULL
SELECT count(*) FROM members m LEFT JOIN orders o ON o.member_id = m.id WHERE o.id IS NULL;

実測結果の比較表

PostgreSQL は EXPLAIN (ANALYZE, FORMAT JSON) の Execution Time、MySQL は EXPLAIN ANALYZE 最上位ノードの actual time を、それぞれ7回計測した中央値です（PostgreSQL は2セット計測した中央値の範囲）。共有のコンテナで計測したため、数十ms程度の揺れは誤差 と見てください。

この表からわかることは次の3つです。

• IN と EXISTS は、PG16 でも MySQL 8.0 でも同じ実行計画になった。 書き方の違いは速度の差になっていません。
• JOIN + DISTINCT は DB によって順位が入れ替わる。 PG16 では両テーブルのインデックスを順に読む Merge Join が効いて最速、MySQL では20万行を結合してから重複を数えるため最も時間がかかりました。
• 否定形は NOT EXISTS と LEFT JOIN IS NULL が anti join（相手に存在しない行だけを残す結合）になり、NOT IN だけが別の計画になる。

PostgreSQL 16：IN と EXISTS の実行計画

IN の実行計画（VACUUM 前の1回目）です。

 Aggregate (actual time=119.557..119.561 rows=1 loops=1)
   ->  Hash Join (actual time=88.412..115.812 rows=73381 loops=1)
         Hash Cond: (m.id = o.member_id)
         ->  Seq Scan on members m (actual time=0.009..8.277 rows=100000 loops=1)
         ->  Hash (actual time=87.898..87.900 rows=73381 loops=1)
               ->  HashAggregate (actual time=68.290..76.759 rows=73381 loops=1)
                     Group Key: o.member_id
                     ->  Seq Scan on orders o (actual time=0.007..25.077 rows=200000 loops=1)
 Execution Time: 122.021 ms

EXISTS も Hash Join → HashAggregate (Group Key: o.member_id) というまったく同じ形で、Execution Time は 105.816 ms でした。PostgreSQL は IN（サブクエリ）も相関EXISTSも「準結合（semi join）」に書き換え、ここでは「注文側の member_id を重複除去してから普通に結合する」方法を選んでいます。

PostgreSQL 16：NOT IN だけが anti join にならない

-- NOT IN
 Aggregate (actual time=116.558..116.560 rows=1 loops=1)
   ->  Seq Scan on members m (actual time=80.917..114.900 rows=26619 loops=1)
         Filter: (NOT (hashed SubPlan 1))
         Rows Removed by Filter: 73381
         SubPlan 1
           ->  Seq Scan on orders o (actual time=0.010..21.321 rows=200000 loops=1)
 Execution Time: 117.086 ms

-- NOT EXISTS
 Finalize Aggregate (actual time=100.161..104.661 rows=1 loops=1)
   ->  Gather (actual time=91.455..104.620 rows=2 loops=1)
         ->  Partial Aggregate (actual time=87.883..87.887 rows=1 loops=2)
               ->  Parallel Hash Right Anti Join (actual time=83.789..87.025 rows=13310 loops=2)
                     Hash Cond: (o.member_id = m.id)
 Execution Time: 104.746 ms

NOT IN は NULL の扱いが anti join と一致しないため、PostgreSQL は サブクエリの結果をハッシュ表にして1行ずつ照合する「hashed SubPlan」 を使います。ハッシュ表がメモリ（work_mem）に収まる間は十分速いのですが、収まらないと次のように大きく変わります。

差が出る条件1：NOT IN でハッシュ表がメモリに収まらない

work_mem を小さくして、ハッシュ表を作れない状況を再現しました（会員は2,000人に絞っています）。

SET work_mem = '64kB';
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM members m
WHERE m.id NOT IN (SELECT o.member_id FROM orders o) AND m.id <= 2000;

 Aggregate (actual time=33543.443..33543.445 rows=1 loops=1)
   ->  Index Only Scan using members_pkey on members m (actual time=571.885..33542.974 rows=192 loops=1)
         Index Cond: (id <= 2000)
         Filter: (NOT (SubPlan 1))
         Rows Removed by Filter: 1808
         SubPlan 1
           ->  Materialize (actual time=0.005..9.552 rows=74017 loops=2000)
                 ->  Seq Scan on orders o (actual time=0.051..36.221 rows=200000 loops=1)
 Execution Time: 33576.670 ms

同じ条件の NOT EXISTS:

 Aggregate (actual time=2.114..2.115 rows=1 loops=1)
   ->  Nested Loop Anti Join (actual time=0.044..2.098 rows=192 loops=1)
         ->  Index Only Scan using members_pkey on members m (actual time=0.015..0.227 rows=2000 loops=1)
               Index Cond: (id <= 2000)
         ->  Index Only Scan using idx_orders_member on orders o (actual time=0.001..0.001 rows=1 loops=2000)
               Index Cond: (member_id = m.id)
 Execution Time: 2.146 ms

NOT IN は約33.6秒、NOT EXISTS は約2.1ms でした。NOT IN はハッシュを使えないと「外側の1行ごとにサブクエリの結果を先頭から読む（loops=2000）」計画になり、インデックスも使われません。NOT EXISTS は anti join に書き換えられるので、idx_orders_member を1行ごとに引くだけで済みます。サブクエリの結果が大きくなりうる否定条件は NOT EXISTS で書く のが確実です。

差が出る条件2：外側の件数が少ないとき

外側を m.id <= 1000 に絞ると、IN も EXISTS も Nested Loop Semi Join に切り替わり、注文側のインデックスを1行ずつ引く計画になりました。

-- EXISTS（IN も同じ形。IN は 1.090 ms）
 Aggregate (actual time=1.218..1.219 rows=1 loops=1)
   ->  Nested Loop Semi Join (actual time=0.018..1.167 rows=905 loops=1)
         ->  Index Only Scan using members_pkey on members m (actual time=0.013..0.101 rows=1000 loops=1)
               Index Cond: (id <= 1000)
         ->  Index Only Scan using idx_orders_member on orders o (actual time=0.001..0.001 rows=1 loops=1000)
               Index Cond: (member_id = m.id)
 Execution Time: 1.240 ms

相関サブクエリでも、結合キーにインデックスがあれば外側の件数×数マイクロ秒で終わります。 性能を左右しているのは IN か EXISTS かではなく、サブクエリ側の結合キー（ここでは orders.member_id）にインデックスがあるか です。

差が出る条件3：EXISTS を OR でつなぐ

SELECT count(*) FROM members m
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.member_id = m.id) OR m.id <= 10;

 Aggregate (actual time=563.933..563.935 rows=1 loops=1)
   ->  Seq Scan on members m (actual time=479.835..560.268 rows=73381 loops=1)
         Filter: ((hashed SubPlan 2) OR (id <= 10))
 Execution Time: 568.922 ms

OR の中に置いた EXISTS は準結合に書き換えられず、hashed SubPlan になります。 AND でつないだ場合と計画が変わるので、遅いときは UNION に分ける、または条件の組み立てを見直すと改善することがあります。

MySQL 8.0 の実行計画

MySQL 8.0.46 では、IN も EXISTS も次の同じ計画になりました。

-> Aggregate: count(0)  (actual time=173..173 rows=1 loops=1)
    -> Nested loop inner join  (actual time=82.8..170 rows=73381 loops=1)
        -> Covering index scan on m using PRIMARY  (actual time=0.684..17.9 rows=100000 loops=1)
        -> Single-row index lookup on <subquery2> using <auto_distinct_key> (member_id=m.id)  (actual time=0.00129..0.00136 rows=0.734 loops=100000)
            -> Materialize with deduplication  (actual time=82.1..82.1 rows=73381 loops=1)
                -> Filter: (o.member_id is not null)  (actual time=0.98..41.6 rows=200000 loops=1)
                    -> Covering index scan on o using idx_orders_member  (actual time=0.979..26.4 rows=200000 loops=1)

サブクエリの結果を 重複除去した一時表（Materialize with deduplication） にして、会員ごとに1行検索する準結合です。NOT EXISTS は同じ形の Nested loop antijoin、LEFT JOIN IS NULL も Nested loop antijoin になりました。

NOT IN は antijoin にはならず、Filter: <in_optimizer>(m.id,m.id in (select #2) is false) というフィルターの下で、重複除去した一時表を1行ずつ引く計画になりました。サブクエリに WHERE o.member_id IS NOT NULL を足しても、この環境では同じ計画（208ms）でした。MySQL リファレンスマニュアルには「比較する式が NULL を取りうる場合、antijoin 変換はほとんどのケースで適用できない」とあり、orders.member_id を NULL 許容で定義していることが理由と考えられます。

MySQL は 8.0.16 以降で EXISTS を準結合に、8.0.17 以降で NOT EXISTS を antijoin に変換できるようになっています（MySQL 8.0 リファレンスマニュアル「Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations」）。「EXISTS の方が速い」「IN は遅い」という説明は、この変換が入る前のバージョンを前提にしたもの です。現在のバージョンでは実行計画で確かめるのが確実です。

MySQLで覚えておきたい制限

IN のサブクエリに LIMIT を書くと、MySQL 8.0 ではエラーになります。

SELECT id FROM d_members
WHERE id IN (SELECT member_id FROM d_orders ORDER BY amount DESC LIMIT 2);

ERROR 1235 (42000) at line 1: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

派生テーブル（FROM句のサブクエリ）で1段包む と動きます。

SELECT id FROM d_members
WHERE id IN (SELECT member_id FROM (
  SELECT member_id FROM d_orders ORDER BY amount DESC LIMIT 2
) t);

id
1
2

PostgreSQL は LIMIT 付きサブクエリを IN の中にそのまま書けます。

ANY / ALL の書き方

IN は = ANY と同じ意味、NOT IN は <> ALL と同じ意味です。ANY / ALL を使うと「〜のどれかより大きい」「〜のすべてより大きい」が書けます。

-- 会員1の注文（1200, 800）のどれよりも大きい注文
SELECT id, amount FROM d_orders
WHERE amount > ALL (SELECT amount FROM d_orders WHERE member_id = 1) ORDER BY id;

-- 会員1の注文のどれか1つより大きい注文
SELECT id, amount FROM d_orders
WHERE amount > ANY (SELECT amount FROM d_orders WHERE member_id = 1) ORDER BY id;

実行結果（PostgreSQL 16）:

 id  | amount 
-----+--------
 103 |   3000
(1 row)

 id  | amount 
-----+--------
 101 |   1200
 103 |   3000
(2 rows)

ALL も NOT IN と同じく NULL の影響を受け、サブクエリが0件のときは常に TRUE になります。

SELECT 5000 > ALL (SELECT amount FROM d_orders) AS no_null,
       5000 > ALL (VALUES (1200),(NULL))       AS with_null,
       5000 > ALL (SELECT 1 WHERE false)       AS empty;

 no_null | with_null | empty 
---------+-----------+-------
 t       |           | t
(1 row)

x > ALL (…) を x > (SELECT max(…) …) に書き換えると、0件のときは max が NULL になり結果が変わる点に注意してください。

相関サブクエリの書き方

相関サブクエリは EXISTS 以外に、SELECT句で「1行ごとの関連値」を取る ときにも使います。

-- 会員ごとの最新注文の金額（注文がなければ NULL）
SELECT m.id, m.name,
       (SELECT o.amount FROM d_orders o
         WHERE o.member_id = m.id
         ORDER BY o.id DESC LIMIT 1) AS last_amount
FROM d_members m
ORDER BY m.id;

実行結果（PostgreSQL 16）:

 id | name | last_amount 
----+------+-------------
  1 | 佐藤 |         800
  2 | 鈴木 |        3000
  3 | 高橋 |            
  4 | 田中 |            
(4 rows)

書くときのポイントは3つです。

別名を付け忘れた例として、WHERE EXISTS (SELECT 1 FROM d_orders WHERE member_id = id) と書くと、id は内側の d_orders.id と解釈され、外側の会員とは無関係な条件になります。相関サブクエリでは外側・内側の両方に別名を付ける と読み違いを防げます。

迷ったときの判断表

よくあるつまずきチェックリスト

よくある質問

Q. EXISTSとINはどちらが速いですか？ A. PostgreSQL 16 と MySQL 8.0 で10万件のデータを試した範囲では、肯定形の IN と EXISTS は同じ実行計画になり、速度差はありませんでした。 速度を左右するのは、サブクエリ側の結合キーのインデックスと、否定形で NOT IN を使っているかどうかです。

Q. NOT IN は使わない方がよいですか？ A. サブクエリの列が NOT NULL 制約付きで、結果が小さいとわかっているなら NOT IN でも正しく動きます。ただし NULL が入ると0件になること、PostgreSQL ではハッシュがメモリに収まらないと大きく遅くなることから、否定の存在チェックは NOT EXISTS を標準にしておく と安心です。

Q. EXISTS のサブクエリは SELECT 1 と SELECT * のどちらがよいですか？ A. EXISTS は行があるかどうかだけを見るため、SELECT句の中身は結果にも性能にも影響しません。意図が伝わる SELECT 1 がおすすめ です。

Q. 相関サブクエリは遅いと聞きました。 A. 論理的には外側の1行ごとに評価しますが、PostgreSQL も MySQL も多くの場合は結合に書き換えます。今回の測定でも、外側1,000件・インデックスありの相関EXISTSは約1.2msでした。遅いときは書き方より先に EXPLAIN で計画とインデックスを確認 してください。

まとめ

• IN は「値がリストに含まれるか」、EXISTS は「サブクエリが1行でも返すか」 で判定する。
• 肯定形の IN と EXISTS は、PG16・MySQL 8.0 とも同じ実行計画 になり、10万件でも速度差は誤差の範囲だった。
• NOT IN はサブクエリに NULL が1つでもあると0件 になる。NOT EXISTS、IS NOT NULL、LEFT JOIN（主キーで IS NULL）で直せる。
• PostgreSQL の NOT IN は anti join にならず、メモリ不足時は 2ms 対 33秒 という差になった。
• JOIN は子の件数ぶん行が増える。存在チェックなら IN / EXISTS、子の列が要るなら JOIN。
• ANY / ALL は IN / NOT IN の一般形。ALL は NULL と0件の扱いに注意。
• 遅いときは書き方を変える前に、結合キーのインデックスと EXPLAIN を確認する。

株式会社RJCでは、SQLのサブクエリと実行計画の読み方を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。