「メール未登録のユーザーだけ抽出したい」。それだけのはずだった。WHERE email = NULL と書いて実行したら、結果は 0件。テーブルにはNULLの行が確かに2つあるのに、だ。正直、最初はデータが壊れたと思って30分詰まった。
結論を先に書く。SQLのNULLは「空文字」でも「ゼロ」でもなく 「値が不明」 という状態を表す。だから = NULL は「不明と一致するか?」という無意味な問いになって、答えは真でも偽でもなく UNKNOWN(不明) に落ちる。NULLを拾うには IS NULL を使う。ここまでは有名だけど、本当に事故るのはその先の NOT IN と集約関数だ。
この記事は、SQLを書き始めて半年くらいの人向け。全部 sqlite3 で手元再現できるコードにした。コピペして叩けば、自分の目で「あ、ほんとに0件だ」を確認できる。
| = NULL の罠 | なぜ0件になるのか、三値論理で説明できる |
| NOT IN 事故 | サブクエリにNULLが1個混ざると全滅する理由 |
| 集約の非対称 | count(*) と count(col) が食い違う仕組み |
| 回避テンプレ | IS NULL / NOT EXISTS / COALESCE の使い分け |
目次
- 症状: 未登録ユーザーが1件も取れない
- 原因: NULLは「値」じゃなく「不明」
- 三値論理: 真でも偽でもない第三の答え
- NOT IN が全滅する: 一番怖い事故
- 集約関数の非対称: count(*) と count(col)
- 連結と足し算にNULLが混ざると全部NULL
- 回避テンプレ: 明日から使う4パターン
- 留意点: NULLを全部潰せばいいわけでもない
- ビフォーアフター
- 今日やること
症状: 未登録ユーザーが1件も取れない
再現テーブルを作る。email が入っている人と、まだ登録していない人(NULL)を混ぜる。
CREATE TABLE users(id INTEGER, name TEXT, email TEXT);
INSERT INTO users VALUES
(1, 'a', 'a@example.com'),
(2, 'b', NULL),
(3, 'c', NULL);
-- 未登録の人だけ取りたい(つもり)
SELECT count(*) FROM users WHERE email = NULL;
期待は2件。実際に返ってくるのは:
0
念のため逆も確認する。「登録済みの人」を <> NULL で取ろうとすると、これも0件になる。
SELECT count(*) FROM users WHERE email <> NULL; -- これも 0
SELECT count(*) FROM users WHERE email IS NULL; -- こっちは 2
= でも <> でも0件、IS NULL だけ2件。最初これを見たとき、テーブルの中身を疑ってDELETEしかけた。悪いのはデータじゃなくて、比較演算子の使い方だった。
ぶっちゃけ、これがアプリのテストで初めて牙をむくと相当ハマった。ローカルでは通っていたテストが、CIで突然こう落ちる。
FAILED tests/test_users.py::test_unregistered_users - assert 0 == 2
「2件返るはずが0件」。SQLは何のエラーも吐かない。文法的には正しいクエリだからだ。エラーが出ないバグは、原因にたどり着くまでが長い。
原因: NULLは「値」じゃなく「不明」
多くのプログラミング言語だと、null や None は「空っぽの値」として == で比較できる。Python なら x == None は普通に True/False を返す。この感覚を引きずってSQLに来ると足をすくわれる。
SQLのNULLは値ですらない。「そこに何が入るべきか不明」 という状態を指すマーカーだ。未入力かもしれないし、存在しないのかもしれないし、単に分からないのかもしれない。だから2つのNULLを見て「同じ値か?」と聞かれても、SQLは「両方とも中身が不明なんだから、一致するとも違うとも言えない」と答える。
SELECT (NULL = NULL); -- 1(真)ではなく、空(=UNKNOWN)
SELECT (NULL IS NULL); -- 1(真)。IS は「状態」を聞いているのでOK
= は「値どうしが等しいか」を聞く演算子。相手が値じゃない以上、答えようがない。一方 IS NULL は「この列はNULLという状態か?」を聞くので、ちゃんと真偽が返る。この区別が全ての起点になる。
三値論理: 真でも偽でもない第三の答え
普通のプログラミングで条件式は True か False の二択。SQLは違って、TRUE / FALSE / UNKNOWN の三値論理(three-valued logic)で動く。NULLが絡む比較は全部この UNKNOWN に落ちる。
SELECT (NULL = 1); -- UNKNOWN(空で返る)
SELECT (NOT (NULL = 1)); -- NOT UNKNOWN もやっぱり UNKNOWN
ここが効いてくる。WHERE 句は 条件がTRUEの行だけ を通す。UNKNOWNの行は「通さない」。FALSEでもないけど通さない。だから email = NULL の行は、TRUEにならないので全部フィルタで落ちる。0件の正体はこれだ。
NOT を付けても救えないのがイヤらしいところ。NOT UNKNOWN は TRUE ではなく UNKNOWN のまま。「等しくない行を取ろう」として WHERE email <> NULL と書いても、UNKNOWNのままなので1件も通らない。二値論理の頭で「じゃあNOTすればいい」と考えると裏切られる。
三値論理のAND/ORも直感とズレる。UNKNOWN AND FALSE は FALSE になる(FALSEが確定なら全体FALSE)けど、UNKNOWN AND TRUE は UNKNOWN のまま。このあたりは深追いすると長くなるので、詳しくは末尾のPostgreSQL公式ドキュメントに譲る。
NOT IN が全滅する: 一番怖い事故
= NULL の罠は有名だから、まだ気づける。本当に事故るのはこっち。サブクエリと NOT IN の組み合わせだ。
「テーブルaにあって、テーブルbには無いidを取る」よくある除外クエリを書く。ただしbにNULLが1個混ざっているとする。
CREATE TABLE a(id INTEGER); INSERT INTO a VALUES (1),(2),(3);
CREATE TABLE b(id INTEGER); INSERT INTO b VALUES (2),(NULL); -- NULLが混入
-- aにあってbに無いid = 期待は 1 と 3 の2件
SELECT count(*) FROM a WHERE id NOT IN (SELECT id FROM b);
返ってくるのは:
0
期待は2件。なのに全滅する。理由を分解するとこうだ。id NOT IN (2, NULL) は
id <> 2 AND id <> NULL
に展開される。後半の id <> NULL は常に UNKNOWN。すると TRUE AND UNKNOWN は UNKNOWN、FALSE AND UNKNOWN は FALSE。どのidを入れてもTRUEにならない。結果、1件も通らない。
これが怖いのは、開発中のテストデータにたまたまNULLが無くて正常に動き、本番でNULLが1件混ざった瞬間に「除外クエリが突然何も返さなくなる」形で牙をむくからだ。自分も本番でこれを踏んで半日溶かした。バグとして再現しづらい。
回避は NOT EXISTS に書き換えること。相関サブクエリなのでNULLに引きずられない。
SELECT count(*) FROM a
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);
-- 結果: 2(正しい)
NOT IN は値のリストが確実にNULLを含まないと分かっている時だけ。サブクエリ相手なら NOT EXISTS を反射で選ぶくらいでちょうどいい。
集約関数の非対称: count(*) と count(col)
もう1つ地味に効くのが集約関数。count(*) と count(列名) は別物だ。
CREATE TABLE s(v INTEGER); INSERT INTO s VALUES (10),(20),(NULL);
SELECT count(*), count(v), sum(v), avg(v) FROM s;
結果:
count(*)=3 count(v)=2 sum(v)=30 avg(v)=15.0
count(*) は行数を数えるので3。count(v) は NULLを除いた 値の数なので2。sum と avg もNULLを無視する。つまり avg(v) は 30 / 3 = 10 ではなく 30 / 2 = 15。分母がこっそり2になっている。
これは仕様として正しい挙動なんだけど、「平均が思ったより高い」の原因がNULLの除外だと気づかないと、数字を読み間違える。「NULLは0として平均に入れたい」なら、集約前に COALESCE(v, 0) で0に潰しておく。潰すかどうかは、そのNULLが「未計測(=平均から除きたい)」なのか「実質0(=平均に入れたい)」なのかで変わる。ここはビジネスロジック側の判断になる。
ちなみに GROUP BY はNULLを 1つのグループにまとめる。比較では「NULL同士は一致しない」のに、グループ化では「NULL同士は同じ籠に入る」。この非対称も覚えておくと混乱しない。
CREATE TABLE g(k TEXT); INSERT INTO g VALUES ('x'),(NULL),(NULL),('x');
SELECT k, count(*) FROM g GROUP BY k;
-- (空), 2
-- x, 2
連結と足し算にNULLが混ざると全部NULL
計算や文字列連結にNULLが1個でも混ざると、結果ごとNULLに飲まれる。
SELECT 'Mr.' || NULL; -- 空(NULL)。'Mr.' にはならない
SELECT 100 + NULL; -- 空(NULL)。100 にはならない
「不明なものを足したら、答えも不明」という理屈で一貫している。氏名を 姓 || ' ' || 名 で組み立てるとき、名がNULLだとフルネームごとNULLになって表示が消える、みたいな地雷がここ。連結する列にNULLの可能性があるなら、COALESCE(名, '') で空文字に落としてから繋ぐ。
MySQLの CONCAT() はNULLを無視する実装だったりと、方言差もある。「NULL連結の挙動はDBによって違う」とだけ頭に置いて、本番DBで一度試すのが安全。
回避テンプレ: 明日から使う4パターン
詰まりどころ別に、そのまま使える形でまとめる。
1. NULL判定は必ず IS NULL / IS NOT NULL
WHERE email IS NULL -- 未登録を取る
WHERE email IS NOT NULL -- 登録済みを取る
= NULL は書いた時点でバグ。エディタのlintで弾けるなら弾く。
2. サブクエリ除外は NOT EXISTS
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id)
NOT IN (サブクエリ) は避ける。どうしても使うなら WHERE id IS NOT NULL をサブクエリ側に足す。
3. 表示・計算の前に COALESCE で既定値へ
SELECT COALESCE(email, '(未登録)') FROM users;
SELECT COALESCE(nickname, name, 'ゲスト') FROM users; -- 左から最初の非NULL
COALESCE は標準SQL。引数を左から見て最初の非NULLを返す。MySQL独自の IFNULL やSQL Server の ISNULL より、COALESCE で統一しておくと移植性が高い。
4. 「等しくない」でNULL行も拾いたいなら明示する
-- status が 'done' 以外(NULL含む)を取りたい
WHERE status <> 'done' OR status IS NULL
WHERE status <> 'done' だけだと、statusがNULLの行はUNKNOWNで落ちる。NULLも「done以外」に含めたいなら OR status IS NULL を足す。ここを忘れると集計から静かに漏れる。
留意点: NULLを全部潰せばいいわけでもない
じゃあ設計段階で全カラム NOT NULL DEFAULT '' にしてNULLを撲滅すればいいのか、というと、そう単純でもない。
NULLは「値が無い」を表現できる唯一の手段でもある。空文字 '' や 0 を「未入力」の代わりに使うと、今度は「本当に空文字を入力した人」と「未入力の人」を区別できなくなる。金額カラムを 0 で埋めると「0円の取引」と「金額不明」が混ざる。NULLを消すこと自体が情報の欠落を生むケースがある。
あと UNIQUE 制約の挙動も知っておくといい。多くのDBで、UNIQUE列にNULLは複数入れられる。「NULL同士は一致しない」ので、一意制約に引っかからないからだ。
CREATE TABLE u(x INTEGER UNIQUE);
INSERT INTO u VALUES (NULL);
INSERT INTO u VALUES (NULL);
INSERT INTO u VALUES (NULL); -- 全部通る。UNIQUE違反にならない
SELECT count(*) FROM u; -- 3
「メールアドレスをUNIQUEにしたから重複しないはず」と思っていても、未登録(NULL)の行は何個でも作れる。ここも仕様として押さえておくと事故らない。NULLは敵じゃなくて、扱い方を知っていれば普通の道具になる。
ビフォーアフター
自分の変化を数字で残しておく。
| Before | After | |
|---|---|---|
| NULL判定 |
= NULL で0件返って30分溶かす |
IS NULL を反射で書く |
| 除外クエリ |
NOT IN を本番で踏んで全滅 |
サブクエリ相手は NOT EXISTS 固定 |
| 平均値の解釈 | avgがズレる理由が分からない | 分母がNULL除外と即答できる |
| 事故率 | NULL起因のクエリバグ 月2〜3回 | 0回(この4パターンで潰した) |
数字で言うと、NULL起因のクエリバグは 月2〜3件 → 0件、原因特定にかかる時間も 30分 → 3分 に縮んだ。結局、一番効いたのは NOT IN を疑うクセがついたこと。「除外クエリで結果が0件になったら、まずサブクエリのNULLを疑う」。実際にこの一言を頭に入れただけで、再現しづらいバグの原因特定が一瞬で済むようになった。
今日やること
-
手元の
sqlite3にこの記事のテーブルをコピペして叩く。= NULLが0件、IS NULLが2件を自分の目で見る。読むだけより10倍記憶に残る -
自分のプロジェクトの
NOT IN (SELECT ...)を grep する。サブクエリ側にNULLが入りうるならNOT EXISTSに書き換える -
= NULL/!= NULLをコード全体で検索。1個でもあればバグなのでIS (NOT) NULLに直す - 集計クエリの
avg/countを見直し、NULL除外が意図通りか確認する
NULLは「空っぽ」じゃなくて「不明」。この一語を握っておくだけで、SQLの理不尽の8割は説明がつく。
参考リンク
- SQLite - NULL Handling — SQLiteでのNULL挙動の公式解説
- PostgreSQL - Comparison Functions and Operators — IS NULL と三値論理の定義
- MySQL - Working with NULL Values — MySQLでのNULL比較とCONCATの挙動
- Wikipedia - Null (SQL) — 三値論理とNOT INの罠の網羅的な整理
- PostgreSQL - COALESCE — COALESCE/NULLIFの仕様