SQL練習室
← SQLガイド

SQLのNOT INとは?NULLが混ざると0件になる理由とNOT EXISTSへの書き換え

NOT INは「候補のどれとも等しくない」行を選ぶ条件ですが、候補にNULLが1つでも混ざると、1行も返らなくなることがあります。チームと社員の表を使い、なぜ0件になるのかを三値論理の結果で確かめ、IS NOT NULLとNOT EXISTSで安全に書き直します。

例で使う表

teamsは1行がチーム1件、employeesは1行が社員1人です。employees.team_idが所属チームのidに対応し、大和は未配属なのでNULLです。営業には2人、開発には1人が所属し、総務には所属社員がいません。ORDER BYを付けた例は行順が決まっています。

teams:チーム3件
idname
1営業
2開発
3総務
employees:社員4人(大和のteam_idはNULL)
idnameteam_id
1青木1
2北野1
3中央2
4大和NULL

NOT INとNULLの基本構文

sql
SELECT *
FROM teams
WHERE id NOT IN (
  SELECT team_id
  FROM employees
  WHERE team_id IS NOT NULL
);

NOT INは候補のどれとも等しくない行を選ぶ

NOT INは、左辺の値が括弧内の候補のどれとも等しくないときに真になります。候補が(1, 2)なら、idが3の総務だけが残ります。営業と開発は候補に含まれるので外れます。候補がNULLを含まない限り、直感どおりの結果です。

sql
SELECT *
FROM teams
WHERE id NOT IN (1, 2)
ORDER BY id;
実行結果
idname
3総務

候補にNULLが混ざるとtrueにならない

NULLは「不明な値」なので、3 = NULLの結果はtrueでもfalseでもなくNULL(不明)です。3 NOT IN (1, 2)はtrueですが、候補にNULLを足すと、1でも2でもない上にNULLとの比較が不明のため、全体がNULLになります。一方、候補に等しい値があれば、NULLが混ざっていてもfalseで確定します。WHEREはtrueの行だけを残すので、NULLの行も落ちます。

sql
SELECT
  3 NOT IN (1, 2) AS without_null,
  3 NOT IN (1, 2, NULL) AS with_null,
  1 NOT IN (1, 2, NULL) AS found;
実行結果
without_nullwith_nullfound
trueNULLfalse

サブクエリの結果にNULLがあると全行が落ちる

社員がいないチームを探すつもりで、候補をサブクエリで作ります。employees.team_idは1、1、2、NULLなので、候補にNULLが入ります。総務のid 3は、1とも2とも等しくありませんが、NULLとの比較が不明になるため条件がtrueにならず、結果は0行です。総務に社員がいないという事実は変わっていません。

sql
SELECT *
FROM teams
WHERE id NOT IN (
  SELECT team_id
  FROM employees
)
ORDER BY id;
実行結果
idname

結果は0行です。

サブクエリ側でIS NOT NULLを付けて除く

候補からNULLを取り除けば、NOT INは直感どおり動きます。内側のWHEREにteam_id IS NOT NULLを付けると、候補は1、1、2だけになり、総務が返ります。大和の未配属はどのチームのidとも対応しないので、候補から外しても意味は変わりません。条件は外側ではなく、候補を作るサブクエリの内側に置きます。

sql
SELECT *
FROM teams
WHERE id NOT IN (
  SELECT team_id
  FROM employees
  WHERE team_id IS NOT NULL
)
ORDER BY id;
実行結果
idname
3総務

左辺がNULLの行はNOT INでも返らない

候補にNULLがなくても、左辺の値がNULLだと結果はNULLです。team_id NOT IN (2, 3)では、青木と北野(チーム1)が返りますが、未配属の大和はNULL NOT IN (2, 3)がNULLになるため外れます。「開発と総務に所属していない社員」に大和を含めたいなら、OR team_id IS NULLを明示します。

sql
SELECT *
FROM employees
WHERE team_id NOT IN (2, 3)
ORDER BY id;
実行結果
idnameteam_id
1青木1
2北野1

NOT EXISTSならNULLを気にせず書ける

NOT EXISTSは、相関条件に合う行が1行もないかを調べます。teams.idとemployees.team_idが等しい社員を探し、見つからなければ残すので、総務が返ります。NULLとの比較は「見つからない」扱いになるだけで、全体をNULLにしません。候補にNULLが入りうる列では、IS NOT NULLを付け忘れる心配のないNOT EXISTSが安全な選択です。

sql
SELECT *
FROM teams
WHERE NOT EXISTS (
  SELECT 1
  FROM employees
  WHERE employees.team_id = teams.id
)
ORDER BY id;
実行結果
idname
3総務

NOT INとNULLでよくある間違い

外側にIS NOT NULLを付けてもNOT INのNULLは消えない

候補のNULLが原因なのに、外側にid IS NOT NULLを足しても直りません。teams.idはNULLではなく、問題は内側のteam_idです。この例も結果は0行のままです。IS NOT NULLはサブクエリの中に書きます。

sql
SELECT *
FROM teams
WHERE id NOT IN (
  SELECT team_id
  FROM employees
)
  AND id IS NOT NULL;

実際に1問解いてみる

NOT IN

teamsから、idが1でも2でもないチームの全列を取得してください。値のリストにはNULLを含めません。行順は問いません。

テーブル

読み込み中…

実行結果

関連構文

NOT INとNULLについてよくある質問

NOT INの結果が0件になるのはバグですか?
バグではなく、SQLの三値論理(true・false・NULL)の仕様です。候補にNULLが1つでもあり、等しい値がなければ、NOT INの結果はNULLになりWHEREで落ちます。PostgreSQLの公式ドキュメントにも同じ説明があります。
NOT INとNOT EXISTSはどちらを使うべきですか?
候補を作る列にNULLが入りうるならNOT EXISTSが安全です。NOT NULL制約がある列や、固定の値リストならNOT INでも問題ありません。NULLの有無で結果が変わらないか、一度確認してから選びます。
NULLを含む候補で「含まれない」をtrueにしたいときは?
候補側のNULLは、サブクエリのWHEREでIS NOT NULLを付けて除きます。左辺のNULLをtrue側に含めたいときは、OR 左辺 IS NULLを足します。どちらも、NULLを扱う方針を条件として書く考え方です。
左辺がNULLのときのNOT INはどうなりますか?
候補が空でない限り、NULL NOT IN (...)の結果はNULLで、WHEREでは残りません。候補が0行のサブクエリなら、左辺がNULLでもNOT INはtrueになります。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。