ダッシュボードの「クーポン利用件数」が、注文テーブルの行数とどうしても合わない。SQLはエラーを一切吐かない。だから合わない理由に気づくまで2時間溶かした。犯人は全部 NULL だった。
NULL は「データが入っていない」くらいの軽い認識でいると、集計の数字を黙って削ってくる。落ちてくれれば気づけるのに、落ちずに「それっぽい値」を返すから厄介で、自分も今回それで詰まった。この記事は、NULL がSQLの集計を静かに壊す代表的な4箇所を、実際に動かせる最小のテーブルで再現する。PostgreSQL 16 で確認したが、挙動はSQL標準（三値論理）に基づくので SQLite でも DuckDB でも同じになる。
NULL と三値論理そのものの基礎は、先に整理してくれている記事がいくつもある。三値論理の真理値表から入りたい人は SQLが従う、NULLを含めた3値論理の真理表、WHERE での NULL 抽出に迷う人は NULLの入ったレコードの抽出で迷わないために が読みやすい。この記事はそこから一歩進めて、「エラーにならずに集計結果がズレる」現場の事故を4つ並べて、各々の直し方まで書く。
何が起きたか — エラーは出ない、数字だけがズレる
題材はシンプルな注文テーブル。1000行のうち、クーポンを使わなかった注文は coupon_id が NULL になっている。
-- 検証用テーブル（PostgreSQL 16 で実行）
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT NOT NULL,
coupon_id INT, -- クーポン未使用なら NULL
amount INT
);
-- 1000行投入。うち coupon_id が NULL なのは 197 行
INSERT INTO orders
SELECT g,
(g % 50) + 1,
CASE WHEN g % 1000 < 197 THEN NULL ELSE (g % 30) + 1 END,
(g % 9000) + 1000
FROM generate_series(1, 1000) AS g;
「クーポンを使った注文は何件か」を出すつもりで、自分は最初こう書いた。
-- クーポン利用件数を出したつもり
SELECT COUNT(coupon_id) FROM orders; -- 803
SELECT COUNT(*) FROM orders; -- 1000
COUNT(coupon_id) は 803、COUNT(*) は 1000。1000件 → 803件 と、197件ぶん消えている。ここまでは「まあそういうものか」で済む。問題は、この 803 をアプリ側で「総注文数」として使い回していたところにあった。テストでようやく落ちた。
Traceback (most recent call last):
File "test_orders.py", line 18, in test_total_matches_rows
assert total == row_count, f"{total} != {row_count}"
AssertionError: 803 != 1000
このアサーションが落ちるまで、ダッシュボードは黙って 803 を「総数」として表示し続けていた。NULL が絡むSQLは、間違っていてもエラーを出さない。 ここが一番のハマりどころで、以降の4箇所も全部「静かに壊れる」点で共通している。
NULL は「不明」であって「無」ではない
直す前に前提を1つ。NULL は 0 でも空文字でもなく、「値が不明」というマーカーだ。だからSQLは TRUE / FALSE の2値ではなく、TRUE / FALSE / UNKNOWN（不明）の三値論理で動く。
NULL = 1 は FALSE ではなく UNKNOWN を返す。NULL = NULL ですら UNKNOWN だ。「不明な値」と「不明な値」が等しいかは、誰にも分からないからだ。
SELECT NULL = 1 AS eq; -- NULL（UNKNOWN）
SELECT NULL = NULL AS eq_null; -- NULL（UNKNOWN）— TRUE ではない
SELECT NULL <> 1 AS neq; -- NULL（UNKNOWN）
そしてSQLの WHERE / 集計 / 制約は、この UNKNOWN をそれぞれ違う形で握りつぶす。「UNKNOWN をどう扱うか」が場所ごとにバラバラなのが、事故が4箇所に散らばる理由になる。この4箇所を順に見ていく。
① COUNT(col) は NULL を数えない
最初の事故がこれ。COUNT(*) は行を数えるが、COUNT(列名) は その列が NULL でない行だけを数える。集約関数は NULL を入力から落とすのがSQL標準の挙動で、PostgreSQL の集約関数ドキュメント にも明記されている。
SELECT
COUNT(*) AS rows_all, -- 1000（行数）
COUNT(coupon_id) AS used_coupon, -- 803（NULL以外）
COUNT(*) - COUNT(coupon_id) AS no_coupon; -- 197（クーポン未使用）
「総注文数」が欲しいなら COUNT(*)。「クーポンを使った注文数」が欲しいなら COUNT(coupon_id)。両方が必要なら上のように1クエリで出してしまうのが安全だ。実際に自分の事故は、この2つを取り違えて COUNT(coupon_id) の結果を総数として扱ったことが発端だった。COUNT(1) が COUNT(*) と同じ意味になる理由も三値論理で説明できて、定数 1 は決して NULL にならないから全行が数えられる。
② NOT IN にNULLが混じると結果がまるごと消える
これが一番こわい。NULL を含むサブクエリを NOT IN に渡すと、マッチするはずの行が1件も返らなくなる。
-- 「クーポンを使っていないユーザーの注文」を出したいが…
SELECT COUNT(*) FROM orders
WHERE user_id NOT IN (
SELECT coupon_id FROM orders -- coupon_id に NULL が混入
);
-- 期待: それなりの件数 / 実際: 1000件 → 0件
理由は三値論理だ。NOT IN (a, b, NULL) は x <> a AND x <> b AND x <> NULL に展開される。最後の x <> NULL は常に UNKNOWN になり、何か AND UNKNOWN は TRUE になれない。結果、全行が脱落して 0件になる。NOT IN とサブクエリの組み合わせは PostgreSQL の比較式ドキュメント でも NULL の扱いに注意と書かれている。
直し方は NOT EXISTS に置き換えるのが定石。NOT EXISTS は行の有無で判定するので、NULL に引きずられない。
-- NOT EXISTS なら NULL に壊されない
SELECT COUNT(*) FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM orders c WHERE c.coupon_id = o.user_id
);
NOT IN を見たら、まずサブクエリ側の列に NULL が入りうるかを疑う。入りうるなら NOT EXISTS か、サブクエリに WHERE col IS NOT NULL を足す。自分は以後、NOT IN (サブクエリ) を書きそうになったら反射で NOT EXISTS に倒すようにした。
③ <> や = はNULL行を黙って外す
WHERE 句は 判定が TRUE の行だけを通す。FALSE はもちろん、UNKNOWN の行も落とす。ここが直感とズレる。
「クーポンID が 5 ではない注文」を coupon_id <> 5 で取ると、coupon_id が NULL の197行は NULL <> 5 = UNKNOWN になって、全部こぼれ落ちる。
-- coupon_id <> 5 のつもりが、NULL行(197件)が消える
SELECT COUNT(*) FROM orders WHERE coupon_id <> 5; -- NULL行は除外される
SELECT COUNT(*) FROM orders
WHERE coupon_id <> 5 OR coupon_id IS NULL; -- NULL行も拾う
「5以外」に「クーポン未使用（NULL）」も含めたいのか、含めたくないのか。仕様として決めて、含めたいなら OR coupon_id IS NULL を明示する。比較演算子と NULL の関係は PostgreSQL の比較演算子ドキュメント にまとまっている。NULL 同士を「等しい」と扱いたいだけなら IS DISTINCT FROM / IS NOT DISTINCT FROM が使える。
-- a, b が両方NULLなら「等しい」とみなしたい時
SELECT 1
WHERE NULL IS NOT DISTINCT FROM NULL; -- TRUE（= だと UNKNOWN）
④ AVG / SUM は分母がNULLぶんズレる
AVG も SUM も、入力の NULL を黙って無視する。SUM は「NULL を 0 として足す」のではなく NULL を最初から計算に入れない。AVG はもっと厄介で、分母（件数）からも NULL が抜ける。
例えば「全注文の平均クーポンID」を出すと、AVG(coupon_id) は 803件で割った平均になる。「NULL は 0 扱い」のつもりで設計していると、本来1000で割るべきところを803で割るので、平均が上振れする。全体の約20%が NULL なら、その差は無視できない。
SELECT
AVG(coupon_id) AS avg_skip_null, -- 803件で割った平均
SUM(coupon_id) / COUNT(*)::numeric AS avg_treat_zero,-- NULLを0扱いした平均
AVG(COALESCE(coupon_id, 0)) AS avg_coalesce; -- 同上を明示的に
どちらが正しいかは仕様次第だ。「クーポンを使った人の中での平均」なら NULL を無視する AVG が正しい。「全注文での平均（未使用は0点）」なら COALESCE(coupon_id, 0) で 0 に寄せてから AVG する。「NULL を無視したいのか、0として扱いたいのか」を毎回決めるのが唯一の防御策で、デフォルト挙動に任せると静かにズレる。
JOIN と GROUP BY での NULL は、また挙動が違う
おまけでもう1つ。JOIN ... ON a.x = b.x の = は NULL 同士をマッチさせない（UNKNOWN になるので結合されない）。一方 GROUP BY は NULL を1つのグループにまとめる。同じ NULL なのに、結合では「別物」、グループ化では「同じ」として扱われる。
-- GROUP BY は NULL を1グループに集める
SELECT coupon_id, COUNT(*)
FROM orders
GROUP BY coupon_id
ORDER BY coupon_id NULLS FIRST;
-- coupon_id = NULL の行に 197 が立つ（NULLが1グループ）
この非対称性を知らないと、「JOINで消えた行が GROUP BY だと残る」逆の現象でまた混乱する。NULL は文脈ごとに = の意味が変わる、と覚えておくと事故が減る。
どう守るか — 設計で寄せて、クエリで明示する
4箇所を見てきて、結局のところ守り方は3層に分かれる。
-
設計層: NULL を許す必要が本当にあるか。「未使用」を NULL ではなく
0や専用の番兵値で持てるなら、NOT NULL制約を付けて NULL を発生させない。これが一番強い。 -
クエリ層: NULL を扱う意図を毎回コードに書く。
COALESCE(col, 0)、col IS NULL、NOT EXISTS、IS DISTINCT FROMのどれかで「NULL をどうしたいか」を明示する。デフォルト挙動に黙って従わない。 -
検証層:
COUNT(*)とCOUNT(col)が一致するか、想定行数と合うかを、アプリのテストで1本アサーションしておく。今回の事故も、テストがあったから2時間で済んだ。無ければ本番で気づいていた。
留意点も正直に書いておく。NOT NULL 制約は、既に NULL が入っている列には後から付けられない。
ALTER TABLE orders ALTER COLUMN coupon_id SET NOT NULL;
ERROR: column "coupon_id" of relation "orders" contains null values
既存データを UPDATE ... SET coupon_id = 0 WHERE coupon_id IS NULL で埋めてからでないと制約は付かない。そして 0 を「クーポン未使用」の意味で使うと、今度は「ID=0 のクーポン」と区別できなくなる。NULL を消すこと自体にもトレードオフがある。万能の正解は無くて、テーブルごとに「NULL を許すか、番兵値で埋めるか」を選ぶしかない。
自分の結論はシンプルで、NULL は「不明」という1点だけを意味させて、それ以外の意味（0・未設定・該当なし）を背負わせない。意味を兼任させた瞬間、COUNT も AVG も NOT IN も、それぞれ別の解釈でズレ始める。エラーにならないぶん、設計とクエリで先回りするしかない領域だ。
参考
- 集約関数 — PostgreSQL Documentation（COUNT/SUM/AVG が NULL を落とす挙動）
- サブクエリ式 — PostgreSQL Documentation（IN / NOT IN と NULL）
- 行・配列比較 — PostgreSQL Documentation（NOT IN の NULL 注意）
- 比較演算子 — PostgreSQL Documentation（IS NULL / IS DISTINCT FROM）
- SQL での null の注意点をおさらいする（WHERE と NULL の基礎をもう一度）