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 にしておく。

setup_orders.sql

-- 再現用テーブル 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行ある」状態を頭の隅に置いておく。

count_null.sql

-- 全体行数と NULL 行数を確認
SELECT count(*) FROM orders;                       -- => 5
SELECT count(*) FROM orders WHERE status IS NULL;  -- => 2

`!=` は NULL 行を数えない

「done 以外」を数える。素直に書くとこうなる。

not_done_wrong.sql

-- 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_compare.sql

-- 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 行を条件に足す。

not_done_fixed.sql

-- 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.sql

-- 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個混じると、結果が丸ごと空になる。

除外リスト用のテーブルを足す。

setup_excluded.sql

-- 除外したい status のリスト。NULL が1行混じっている
CREATE TABLE excluded (status TEXT);
INSERT INTO excluded (status) VALUES ('done'), (NULL);

excluded に載っていない注文を取りたい、と NOT IN で書く。

not_in_wrong.sql

-- 「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 は正常に戻る。

not_in_filter_null.sql

-- サブクエリで 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.sql

-- 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 = 未設定 = 空文字扱い」と決められる場合だけ。

coalesce.sql

-- 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_diff.sql

-- 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 を疑う。