はじめに
この記事では、MySQL で NOT IN のサブクエリに NULL が含まれると行が返らなくなる理由と、NOT EXISTS との違いを、クエリを動かしながら整理しました。
実行環境
ブラウザ上で SQL を実行できる DB Fiddle を使い、データベースに MySQL 8.0 を選択しました。
サンプルテーブル
生徒を表す students と、テストの点数を表す scores の2つのテーブルを使用します。
scores には渡辺さん（id = 6）の行がありません。この記事では、渡辺さんのように成績が1件もない生徒を取得します。
テーブルの定義とデータは以下のとおりです。
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
class VARCHAR(10)
);
CREATE TABLE scores (
id INT PRIMARY KEY,
student_id INT,
subject VARCHAR(10),
score INT
);
INSERT INTO students VALUES
(1, '佐藤', 'A組'),
(2, '鈴木', 'A組'),
(3, '高橋', 'A組'),
(4, '田中', 'B組'),
(5, '伊藤', 'B組'),
(6, '渡辺', 'B組');
INSERT INTO scores VALUES
(1, 1, '国語', 75),
(2, 2, '国語', 60),
(3, 3, '国語', 85),
(4, 4, '国語', 55),
(5, 5, '国語', 90),
(6, 1, '数学', 80),
(7, 2, '数学', NULL),
(8, 3, '数学', 65),
(9, 4, '数学', 90),
(10, 5, '数学', 70);
NOT IN で成績が1件もない生徒を取得する
scores の student_id に含まれていない生徒を NOT IN で取得します。
SELECT *
FROM students
WHERE id NOT IN (SELECT student_id FROM scores);
( ) の中の SELECT student_id FROM scores がサブクエリです。サブクエリは scores の student_id の一覧（1, 2, 3, 4, 5, 1, 2, 3, 4, 5）を返し、WHERE id NOT IN (...) はその一覧に id が含まれない生徒だけを残します。
実行結果は以下のとおりです。
| id | name | class |
|---|---|---|
| 6 | 渡辺 | B組 |
記名漏れの答案を追加すると0行になる
名前が書かれていない答案を、student_id が NULL の行として scores に追加します。
INSERT INTO scores VALUES (11, NULL, '国語', 50);
VALUES の値は id・student_id・subject・score の順に対応しており、student_id に NULL を入れています。
この状態で、成績が1件もない生徒を NOT IN で取得するクエリを実行すると、結果は0行になります。
SELECT *
FROM students
WHERE id NOT IN (SELECT student_id FROM scores);
-- 結果：0行
追加した行は渡辺さんとは関係がありませんが、渡辺さんの行も結果に含まれなくなります。
なぜ0行になるのか
リファレンスマニュアルには、NOT IN は <> ALL の別名だと書かれています。そのため、このクエリの条件は次のように書き換えられます。
WHERE id <> ALL (SELECT student_id FROM scores)
<> は等しくないことを判定する比較演算子です。<> ALL は、id とサブクエリが返す値を1つずつ <> で比較し、すべての比較が TRUE のときに TRUE を返します。1つでも FALSE になる比較があれば FALSE になり、FALSE がなく NULL になる比較が含まれる場合は NULL になります。
記名漏れの答案を追加すると、サブクエリが返す値に NULL が含まれます。渡辺さん（id = 6）についての比較は以下のとおりです。
6 <> 1 から 6 <> 5 まではすべて TRUE ですが、6 <> NULL だけは NULL になります。NULL との比較の結果は NULL になるためです。すべての比較が TRUE ではないため <> ALL は TRUE にならず、FALSE になる比較もないため、全体の結果は NULL になります。
生徒ごとの id NOT IN (...) の結果は以下のとおりです。
MySQL では WHERE 句の条件が NULL になった行も FALSE と同じように扱われるため、id が 1〜5 の生徒（FALSE）も、渡辺さん（NULL）も除外されます。その結果、1行も返らなくなります。
マニュアルの ALL を説明するページでも、サブクエリを書くときは NULL を含むテーブルを考慮するよう注意されています。
サブクエリに NULL があっても成績が1件もない生徒を取得する
NOT EXISTS を使う
NOT EXISTS は、サブクエリの条件に一致する行が1件もないときに TRUE、1件でもあるときに FALSE になります。
SELECT *
FROM students s
WHERE NOT EXISTS (
SELECT * FROM scores sc
WHERE sc.student_id = s.id
);
サブクエリは、生徒ごとに scores からその生徒の id と一致する student_id を持つ行を探します。
EXISTS は条件に一致する行があるかどうかだけを判定するため、サブクエリの SELECT に書く列は結果に関係しません。EXISTS のサブクエリは慣例として SELECT * と書かれます。
佐藤さんと渡辺さんを例に、NOT EXISTS の判定の流れは以下のとおりです。
渡辺さんの行を評価するとき、記名漏れの答案は NULL = 6 が NULL になるため、サブクエリの結果に含まれません。一致する行が1件もないため、NOT EXISTS は TRUE になり、実行結果は渡辺さんの1行になります。
NOT EXISTS は、サブクエリが行を返すかどうかだけで TRUE か FALSE を決めるため、NOT IN のように NULL になることがありません。
サブクエリで NULL を除外する
NOT IN を使う場合は、サブクエリの結果から NULL を取り除きます。
SELECT *
FROM students
WHERE id NOT IN (
SELECT student_id FROM scores
WHERE student_id IS NOT NULL
);
WHERE student_id IS NOT NULL によって、記名漏れの答案（scores の id = 11 の行）がサブクエリの結果から外れます。
サブクエリが NULL を返さなくなるため、渡辺さんの比較はすべて TRUE になり、実行結果は渡辺さんの1行になります。
NOT IN と NOT EXISTS の使い分け
NOT IN は、サブクエリの結果に NULL があると、その値との比較が NULL になり、どの行も TRUE になりません。NOT EXISTS は、条件に一致する行があるかどうかだけで判定するため、サブクエリの結果に NULL があっても結果が変わりません。
このサンプルでは students.id が主キーで NULL にならないため、違いが出るのはサブクエリの結果に NULL が含まれる場合だけです。そのため、サブクエリが参照する列に NULL が入るかどうかで使い分けられます。
| サブクエリが参照する列 | NOT IN |
NOT EXISTS |
|---|---|---|
NULL が入らない（NOT NULL を指定した列など） |
そのまま使える | そのまま使える |
| NULL が入る可能性がある | サブクエリで NULL を除外する必要がある | そのまま使える |
サブクエリではなく値を直接並べて指定する場合は、自分で NULL を入れない限り NULL が含まれないため、NOT IN をそのまま使えます。
SELECT *
FROM students
WHERE id NOT IN (1, 2, 3);
実行結果は、田中さん・伊藤さん・渡辺さんの3行になります。
まとめ
-
NOT INのサブクエリに NULL が含まれると、比較の結果が NULL になり、1行も返らない -
NOT EXISTSは一致する行があるかどうかで判定するため、NULL の影響を受けない - サブクエリの列に NULL が入る可能性がある場合は、
NOT EXISTSを使うかIS NOT NULLで NULL を除外する
参考
Claudeが応答を完了しました