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を付けた例は行順が決まっています。
| id | name |
|---|---|
| 1 | 営業 |
| 2 | 開発 |
| 3 | 総務 |
| id | name | team_id |
|---|---|---|
| 1 | 青木 | 1 |
| 2 | 北野 | 1 |
| 3 | 中央 | 2 |
| 4 | 大和 | NULL |
NOT INとNULLの基本構文
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を含まない限り、直感どおりの結果です。
SELECT *
FROM teams
WHERE id NOT IN (1, 2)
ORDER BY id;| id | name |
|---|---|
| 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の行も落ちます。
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_null | with_null | found |
|---|---|---|
| true | NULL | false |
サブクエリの結果にNULLがあると全行が落ちる
社員がいないチームを探すつもりで、候補をサブクエリで作ります。employees.team_idは1、1、2、NULLなので、候補にNULLが入ります。総務のid 3は、1とも2とも等しくありませんが、NULLとの比較が不明になるため条件がtrueにならず、結果は0行です。総務に社員がいないという事実は変わっていません。
SELECT *
FROM teams
WHERE id NOT IN (
SELECT team_id
FROM employees
)
ORDER BY id;| id | name |
|---|
結果は0行です。
サブクエリ側でIS NOT NULLを付けて除く
候補からNULLを取り除けば、NOT INは直感どおり動きます。内側のWHEREにteam_id IS NOT NULLを付けると、候補は1、1、2だけになり、総務が返ります。大和の未配属はどのチームのidとも対応しないので、候補から外しても意味は変わりません。条件は外側ではなく、候補を作るサブクエリの内側に置きます。
SELECT *
FROM teams
WHERE id NOT IN (
SELECT team_id
FROM employees
WHERE team_id IS NOT NULL
)
ORDER BY id;| id | name |
|---|---|
| 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を明示します。
SELECT *
FROM employees
WHERE team_id NOT IN (2, 3)
ORDER BY id;| id | name | team_id |
|---|---|---|
| 1 | 青木 | 1 |
| 2 | 北野 | 1 |
NOT EXISTSならNULLを気にせず書ける
NOT EXISTSは、相関条件に合う行が1行もないかを調べます。teams.idとemployees.team_idが等しい社員を探し、見つからなければ残すので、総務が返ります。NULLとの比較は「見つからない」扱いになるだけで、全体をNULLにしません。候補にNULLが入りうる列では、IS NOT NULLを付け忘れる心配のないNOT EXISTSが安全な選択です。
SELECT *
FROM teams
WHERE NOT EXISTS (
SELECT 1
FROM employees
WHERE employees.team_id = teams.id
)
ORDER BY id;| id | name |
|---|---|
| 3 | 総務 |
NOT INとNULLでよくある間違い
外側にIS NOT NULLを付けてもNOT INのNULLは消えない
候補のNULLが原因なのに、外側にid IS NOT NULLを足しても直りません。teams.idはNULLではなく、問題は内側のteam_idです。この例も結果は0行のままです。IS NOT NULLはサブクエリの中に書きます。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:NOT IN(サブクエリ) — NULLが混ざる場合のNOT INの結果と、空のサブクエリの扱い
- PostgreSQL公式:EXISTS — EXISTSと、相関する外側の値の扱い
- PostgreSQL公式:NOT IN(値のリスト) — 値のリストに対するNOT INとNULLの結果