WHERE status != 'done' で「完了以外」を数えたら、行数が合わない。全5件のうち期待は4件、返ってきたのは2件。5件→2件で、3件も足りない。消えた行は status が NULL だった。
原因は SQL の三値論理。NULL != 'done' は TRUE でも FALSE でもなく「UNKNOWN」で、WHERE は UNKNOWN の行を通さない。だから「done ではない」と書いたつもりでも、NULL 行は黙って落ちる。
この記事は、その落ち方を実際のクエリと行数で再現して、NOT IN で結果が 2件→0件 に全滅するもっと怖いパターンまで潰す。対象は SQL を書くけど三値論理を意識したことがない人。PostgreSQL / MySQL / SQLite で挙動を確認した。ぶっちゃけ一度は全員踏む罠。
@[toc]
NULL 混じりのテーブルを1つ用意する
再現用に、注文テーブルを1つ作る。status の一部を意図的に NULL にしておく。
-- 再現用テーブル orders。status に NULL を混ぜる
CREATE TABLE orders (
id INT PRIMARY KEY,
status TEXT
);
INSERT INTO orders (id, status) VALUES
(1, 'done'),
(2, 'pending'),
(3, 'pending'),
(4, NULL), -- ステータス未設定の行
(5, NULL);
全体は5行。うち done が1行、pending が2行、NULL が2行。NULL の割合は 40% ある。この「NULL が2行ある」状態を頭の隅に置いておく。
-- 全体行数と NULL 行数を確認
SELECT count(*) FROM orders; -- => 5
SELECT count(*) FROM orders WHERE status IS NULL; -- => 2
!= は NULL 行を数えない
「done 以外」を数える。素直に書くとこうなる。
-- done 以外を数えたつもり
SELECT count(*) FROM orders WHERE status != 'done';
-- => 2
期待は「全5行のうち done の1行を除いた4行」。でも返ってきたのは2行。5件→2件で、pending の2行だけ。NULL の2行が入っていない。
理由は status != 'done' を1行ずつ評価したとき、NULL 行だけ結果が UNKNOWN になるから。
| status |
status != 'done' の結果 |
WHERE が通すか |
|---|---|---|
'done' |
FALSE | 通さない |
'pending' |
TRUE | 通す |
NULL |
UNKNOWN | 通さない |
WHERE 句は「TRUE の行だけ」を返す。FALSE と UNKNOWN はどちらも捨てる。NULL 行が UNKNOWN で捨てられるので、!= は NULL を「done ではない」に数えてくれない。
PostgreSQL の比較演算子のドキュメントにもこう書いてある。NULL との比較はほぼすべて NULL（=UNKNOWN）を返す、と(PostgreSQL: Comparison Functions and Operators)。
なぜ TRUE でも FALSE でもなく UNKNOWN なのか
SQL の NULL は「値が無い」ではなく「値が不明」を表す。だから NULL 同士の比較すら不明になる。
-- NULL との比較はすべて NULL を返す
SELECT (NULL = NULL); -- => NULL（TRUE ではない）
SELECT (NULL != 'done'); -- => NULL
SELECT (1 = NULL); -- => NULL
「不明な値」が 'done' と等しいかどうかは、決められない。等しいとも等しくないとも言えないから、答えは UNKNOWN になる。この TRUE / FALSE / UNKNOWN の3状態で動くのが三値論理。二値論理（TRUE / FALSE だけ）に慣れた頭だと、ここで必ず一度つまずく。正直、自分も最初は「バグでは？」と疑った。
MySQL のマニュアルも「NULL の比較には = や != ではなく IS NULL を使え」と明記している(MySQL: Working with NULL Values)。
直し方は「NULL を明示的に拾う」
「done ではない、または未設定」を数えたいなら、NULL 行を条件に足す。
-- OR で NULL 行を明示的に拾う
SELECT count(*) FROM orders
WHERE status != 'done' OR status IS NULL;
-- => 4
これで期待どおり 2件→4件 に戻る。pending の2行 + NULL の2行。
PostgreSQL なら IS DISTINCT FROM が短い。NULL を通常の値のように扱って比較してくれる演算子で、NULL 同士は「区別しない=等しい」と判定する。
-- IS DISTINCT FROM は NULL を値として比較する（PostgreSQL / SQLite）
SELECT count(*) FROM orders
WHERE status IS DISTINCT FROM 'done';
-- => 4
status が NULL の行も 'done' とは「区別される（DISTINCT）」ので TRUE になり、ちゃんと数えられる。MySQL には同じ意味の <=>（NULL-safe equal）があり、否定は NOT (status <=> 'done') で書ける。
NOT IN + NULL は結果を全滅させる
!= は「消える行がある」で済む。もっと怖いのが NOT IN にサブクエリを渡したケース。NULL が1個混じると、結果が丸ごと空になる。
除外リスト用のテーブルを足す。
-- 除外したい status のリスト。NULL が1行混じっている
CREATE TABLE excluded (status TEXT);
INSERT INTO excluded (status) VALUES ('done'), (NULL);
excluded に載っていない注文を取りたい、と NOT IN で書く。
-- 「done でない」注文が欲しい。でも…
SELECT count(*) FROM orders
WHERE status NOT IN (SELECT status FROM excluded);
-- => 0
pending の2行くらいは返ってほしいのに、結果は 2件→0件 で全滅。
NOT IN (a, b) は status != a AND status != b に展開される。リストが ('done', NULL) なので、
status != 'done' AND status != NULL
status != NULL はどの行でも UNKNOWN。TRUE AND UNKNOWN は UNKNOWN、FALSE AND UNKNOWN は FALSE。つまりどう転んでも TRUE にならない。だから WHERE を通る行が1行も無くなる。pending の行ですら消える。
これは「NULL 行だけ落ちる」より質が悪い。NULL と無関係に見える行まで巻き添えで消える。しかも構文エラーにならないので、気づくのは「なんか0件だな」と手で数え直したとき。
NOT IN の直し方は3つ
1. サブクエリ側で NULL を捨てる
一番手軽。除外リストから NULL を落とせば NOT IN は正常に戻る。
-- サブクエリで NULL を除外してから NOT IN に渡す
SELECT count(*) FROM orders
WHERE status NOT IN (
SELECT status FROM excluded WHERE status IS NOT NULL
);
-- => 2（pending の2行）
これで 0件→2件 に戻る。
2. NOT EXISTS に書き換える
NOT EXISTS は行の有無だけを見るので、NULL の三値論理に巻き込まれない。件数が増えても速いことが多く、個人的にはこれを既定にしている。
-- NOT EXISTS は NULL の影響を受けない
SELECT count(*) FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM excluded e WHERE e.status = o.status
);
-- => 3
ただし結果は3件。NOT IN（NULL 除去版）の2件と1件ずれる。差は orders 側の NULL 行の扱い。NOT EXISTS は「excluded に一致行が無ければ残す」ので、o.status が NULL の行は e.status = NULL がどの行とも一致せず、残る。NOT IN 版は orders 側 NULL がやはり UNKNOWN で落ちる。
どちらが正しいかは要件次第。「未設定の注文をどう扱うか」を先に決めないと、この1件差はどの書き方でも詰められない。ここが NULL のいやらしいところで、書き方の問題に見えて、実は仕様の穴を突かれている。
3. COALESCE で NULL を既定値に潰す
比較の前に NULL を確定値へ寄せる方法。意味が変わるので、業務的に「NULL = 未設定 = 空文字扱い」と決められる場合だけ。
-- NULL を空文字に寄せてから比較する
SELECT count(*) FROM orders
WHERE COALESCE(status, '') NOT IN ('done');
-- => 4
COALESCE の挙動も PostgreSQL の条件式ドキュメントに明記されている(PostgreSQL: Conditional Expressions)。NOT IN とサブクエリの NULL の相互作用も別ページに書いてある(PostgreSQL: Subquery Expressions)。「知らないと踏む」ではなく「仕様として書いてある」挙動。
集計とJOINでも同じ罠が出る
WHERE だけの話ではない。NULL は集計と JOIN でも静かに数字をずらす。
count(column) は NULL をスキップする。count(*) は行を数える。この違いで平均が変わる。
-- count(*) と count(status) はズレる
SELECT count(*) AS all_rows, -- => 5
count(status) AS non_null -- => 3（NULL の2行は数えない）
FROM orders;
5件→3件で、分母が2件も減る。AVG や SUM も NULL を無視して計算するので、「分母が思ったより小さい平均」が出る。データ分析で NULL 混じりの列を平均するときは、count(*) との差を必ず一度見ておく。
JOIN では、結合キーが NULL の行は ON a.key = b.key で一致しないので INNER JOIN から落ちる。「件数が減った」ときはまず結合キーの NULL 率を疑う。
SQLite も同じ三値論理で動く。実装ごとの細かな差はあるが、= NULL が UNKNOWN になる原則は共通で、sqlite.org の NULL 解説にも同じ説明がある。SQL Server も NULL と UNKNOWN の扱いを公式に説明している(Microsoft: NULL and UNKNOWN)。
デメリットと留意点
NULL を潰す方向（COALESCE で空文字に寄せる、NOT NULL 制約を全列に付ける）は万能ではない。
-
COALESCE(status, '')は「未設定」と「空文字が入っている」を区別できなくする。両者を分けたい業務では情報が消える。 - 全列
NOT NULL+ 既定値は一見安全だが、「未入力」を''や0で埋めると、集計時に実データと混ざる。NULL のままの方が「不明」を正直に表せる場面は多い。 -
IS DISTINCT FROMや<=>は可読性が上がる代わりに、インデックスが効きにくいことがある。件数の多いテーブルでは実行計画を一度見る。
つまり「NULL を消す」より「NULL をどう扱うと決めて、条件に明示的に書く」方が安全なことが多い。落とすのか拾うのかを、クエリを書く前に言語化しておく。
まとめと今日からのアクション
!= で行が消えるのも、NOT IN で結果が 2件→0件 に全滅するのも、根っこは同じ。NULL との比較が UNKNOWN になり、WHERE が UNKNOWN を通さない。
-
今日: 自分のコードベースで
NOT IN (SELECT ...)を grep する。サブクエリの列が NULL を含み得るならNOT EXISTSかWHERE ... IS NOT NULLに直す。 -
今週:
!=/<>で除外条件を書いている箇所を洗い、対象列が NULL を取り得るか確認する。取り得るならOR col IS NULLかIS DISTINCT FROMを足す。 - 今月: 「未設定をどう扱うか」を列ごとに決めてスキーマにメモを残す。NULL 許可なら、その列を使う集計・除外条件をレビュー観点に入れる。
NULL は「バグ」ではなく仕様。仕様どおりに黙って行を落とすから、テストで気づきにくい。行数が合わない朝は、まず NULL を疑う。