SQLでサブクエリを書くとき、INとEXISTSは同じ結果を返すように見える場面がよくあります。しかし、サブクエリの結果にNULLが含まれる場合は動作が大きく異なり、特にNOT INは意図した結果を返さない「NULLトラップ」として知られています。この記事では、INとEXISTSの動作の違い、NOT INが抱えるNULL問題、そしてMySQL 8.0での性能の考え方を実例つきで整理します。
このシナリオで考える
顧客テーブル（customers）と注文テーブル（orders）を使います。orders.customer_idはNULLを許容しており、「顧客不明の注文」が1件含まれています。この設定が、後ほど解説するNULL問題を再現するポイントになります。
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT, -- NULL を許容（顧客不明）
amount INT NOT NULL
);
INSERT INTO customers VALUES
(1, '田中'), (2, '鈴木'), (3, '佐藤');
INSERT INTO orders VALUES
(101, 1, 5000),
(102, 1, 3000),
(103, 2, 8000),
(104, NULL, 1200); -- 顧客不明の注文田中・鈴木は注文あり、佐藤は注文なし、という状態です。
INとEXISTSの基本動作
INはサブクエリが返す値のリストと照合し、一致があればTRUEを返します。
-- 注文がある顧客を取得（IN版）
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);+------+
| name |
+------+
| 田中 |
| 鈴木 |
+------+EXISTSはサブクエリが1行でも返すかどうかを確認します。返す値の内容は問わないため、慣例としてSELECT 1が使われます。外側の各行に対してサブクエリを評価する「相関サブクエリ」と組み合わせるのが一般的です。
-- 注文がある顧客を取得（EXISTS版）
SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);結果は同じく田中・鈴木が返ります。通常の検索ではどちらを使っても動作の差はありません。2つの基本的な違いを整理します。
| 観点 | IN | EXISTS |
|---|---|---|
| 動作の仕組み | 値のリストと照合 | 行の存在を確認 |
| サブクエリの形 | 非相関・相関どちらも可 | 主に相関サブクエリ |
| 返しうる値 | TRUE / NULL / FALSE | TRUE / FALSE のみ |
| 短絡評価 | なし（全件照合が基本） | 最初の一致で打ち切り |
NOT INのNULL問題とNOT EXISTSの安全性
最も重要な違いは、否定形のときに現れます。「注文がない顧客」（佐藤）を取得したいとします。
-- 注文のない顧客を取得しようとする（NOT IN版）
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);期待する結果は「佐藤」ですが、実際は0件が返ります。サブクエリが (1, 2, NULL) を返すため、NOT INのNULLトラップが発動します。
SQLの三値論理（TRUE / FALSE / NULL）では、NULLとの等値比較は必ずNULLになります。NOT INはAND条件で全要素を評価するため、1つでもNULLがあると最終結果がNULLになります。
| 評価式 | 結果 | 理由 |
|---|---|---|
| 3 <> 1 | TRUE | 一致しない |
| 3 <> 2 | TRUE | 一致しない |
| 3 <> NULL | NULL | NULLとの比較は常にNULL |
| TRUE AND TRUE AND NULL | NULL | AND条件でNULLが1つでもあると全体がNULL |
WHERE句はNULLをTRUEとして扱わないため、佐藤を含む全行が除外されます。NOT EXISTSを使えばこの問題を回避できます。
-- 正しい方法：NOT EXISTS を使う
SELECT name FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);+------+
| name |
+------+
| 佐藤 |
+------+NOT EXISTSは「該当する行が存在しないこと」を確認するだけで、NULLとの等値比較を行いません。サブクエリにNULLが含まれていても正しく動作します。
NOT INを使いたい場合はIS NOT NULLで絞り込む方法もあります。
-- NOT IN でも IS NOT NULL を追加すれば安全
SELECT name FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders
WHERE customer_id IS NOT NULL
);性能の違いと実行計画の見方
MySQL 5.5以前は「EXISTSの方が速い」と言われることがありましたが、MySQL 8.0ではオプティマイザが IN (サブクエリ) をセミジョイン（semi-join）に変換して最適化するため、多くのケースで性能差は小さくなっています。
| ケース | IN | EXISTS |
|---|---|---|
| 非相関サブクエリ | ○ セミジョイン変換で効率的 | △ 相関前提のため不自然 |
| 相関サブクエリ | △ | ○ 最初の一致で打ち切り |
| サブクエリ結果が大量 | インデックス次第 | 短絡評価で有利なことも |
| NOT付き・NULL混在列 | × 結果が壊れる | ○ 安全 |
実行計画の確認にはEXPLAINを使います。
EXPLAIN SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);orders.customer_idにインデックスがあればtype: refで高速に動作します。インデックスがないとtype: ALL（全件スキャン）になり、外側の件数×内側の全件スキャンが発生します。セミジョイン変換が有効なときはEXPLAINのExtra列にStart temporary; End temporaryが表示されます。
よくある落とし穴 / 注意点
- NOT INはNULL混在列には使わない: 対象列がNULLを許容するなら
NOT EXISTSを使うか、WHERE col IS NOT NULLを追加します。設計段階で対象列をNOT NULL制約にできれば根本解決になります。 - 相関EXISTSには相関列のインデックスが必須: 外側のテーブルが大きく、相関列にインデックスがない場合はO(n²)に近いスキャンが起きます。必ずインデックスを設定します。
- IN（リテラルリスト）とINサブクエリは別物:
WHERE id IN (1, 2, 3)のようなリテラルリストはNULLの問題が起きません。サブクエリを使うときだけ注意が必要です。 - MySQL 8.0.16以降のdecorelation: EXISTSの相関述語が自明な場合、オプティマイザがINに変換して最適化できることがあります。IN/EXISTSの選択よりも、インデックス設計の方が性能への影響は大きいです。
まとめ
通常の検索ではINとEXISTSはほぼ同じ結果になります。しかし否定形では違いが致命的になります。NOT INはサブクエリ結果にNULLが1件でもあると全件0になるため、否定の場合は原則としてNOT EXISTSを選びましょう。性能面ではMySQL 8.0のセミジョイン最適化で差が縮まっていますが、相関列のインデックス設計の方が実際の性能への影響は大きいです。
参考リンク
- MySQL公式リファレンス: Subqueries with EXISTS or NOT EXISTS
- MySQL公式リファレンス: Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations
- MySQL公式リファレンス: Optimizing Subqueries with the EXISTS Strategy
アイキャッチ画像: Photo by Shubham Dhage on Unsplash
