· 開発

NOT IN の結果が0件になる理由：SQL の NULL と3値論理

サブクエリの結果に NULL が1つでもあると NOT IN の条件で0件になる理由を、PostgreSQL・MySQL・SQLite の公式ドキュメントをもとに整理しました。3値論理、WHERE が行を残す条件、NOT EXISTS での直し方も扱います。

開発データベースSQL

ブロックリストに入っていないユーザーだけを取り出すために、次のようなクエリを書くことがあります。

SELECT id FROM users
WHERE id NOT IN (SELECT user_id FROM blocked);

blocked.user_id に NULL が1行でもあると、このクエリは1件も返しません。エラーも警告も出ません。原因は、SQL が比較の結果を真と偽の2つではなく、「不明」を加えた3つで扱うことにあります。

この記事は PostgreSQL、MySQL、SQLite の公式ドキュメントを根拠にしています。ISO SQL 規格の原文は確認していません。サブクエリを使う NOT IN がこう動くこと、そしてそれが NULL を含む論理式についての SQL の通常のルールに従った結果であることは、PostgreSQL のドキュメントに書かれています。MySQL のマニュアルは、値リストを使う IN() について同じ NULL のルールを別に説明し、SQL 規格に準拠するための動作だとしています。

NULL と比較すると結果も NULL になる

PostgreSQL のドキュメントによると、通常の比較演算子は入力のどちらかが NULL なら、真でも偽でもなく NULL を返します。この NULL は「不明」を意味します。7 = NULL も、7 <> NULL も結果は NULL です。NULL かどうかを調べるときに = NULL ではなく IS NULL を使うのも同じ理由です。

論理演算では、NULL は次のように伝わります。

式 結果
TRUE AND NULL NULL
FALSE AND NULL FALSE
NOT NULL NULL

NOT IN を展開してみる

PostgreSQL のドキュメントは、値リストに対する NOT IN を、<> の比較を AND でつないだ式の省略形だと説明しています。すると 1 NOT IN (2, NULL) は次のように展開されます。

1 <> 2 AND 1 <> NULL
-- TRUE AND NULL → NULL

1つ目の比較は真ですが、2つ目が NULL なので全体も NULL です。2 NOT IN (2, NULL) は 2 <> 2 が偽なので全体が偽になります。右側に NULL が1つでもあれば、NOT IN の結果は偽か NULL のどちらかで、真にはなりません。

サブクエリでもルールは同じです。PostgreSQL のドキュメントには、等しい値がなく、サブクエリの結果に NULL が1行でもあれば NOT IN の結果は真ではなく NULL になると書かれています。MySQL のマニュアルは、IN() は一致する値が見つからず、リストに NULL があれば NULL を返すと説明し、NOT IN を NOT (expr IN (...)) と同じものと定義しています。SQLite のドキュメントの結果表も同じです。

WHERE は真の行だけを残す

PostgreSQL のドキュメントによると、WHERE は条件が真の行だけを結果に残し、偽または NULL の行は捨てます。NULL を含む NOT IN はすべての行で偽か NULL になるため、結果は0件になります。

IN では同じ問題がほとんど表に出ません。id IN (2, NULL) なら、id が2の行は真なので残り、ほかの行は NULL なので外れます。偽だったとしても外れていた行なので、結果は普通に見えます。PostgreSQL のドキュメントも、NULL でつまずきやすいのは IN より NOT IN のほうなので、できれば条件を肯定形で書くよう勧めています。

SQLite で再現した結果

以下は SQLite 3.43.2 で実際に実行した結果です。

CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE blocked(user_id INTEGER);

INSERT INTO users VALUES (1,'a'), (2,'b'), (3,'c');
INSERT INTO blocked VALUES (2), (NULL);
クエリ 結果
WHERE id NOT IN (SELECT user_id FROM blocked) 0件
WHERE id IN (SELECT user_id FROM blocked) 2
NOT EXISTS 1, 3
NOT IN ＋サブクエリの WHERE user_id IS NOT NULL 1, 3

直し方

方法1は NOT EXISTS に書き換えることです。PostgreSQL のドキュメントによると、EXISTS はサブクエリが1行でも返せば真、1行も返さなければ偽です。SQLite のドキュメントも、EXISTS は常に0か1になり、NULL を含む行も特別には扱わないと説明しています。結果が NULL になることがないので、上の落とし穴は起きません。

SELECT id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b WHERE b.user_id = u.id
);

方法2はサブクエリから NULL を除くことです。

SELECT id FROM users
WHERE id NOT IN (
  SELECT user_id FROM blocked
  WHERE user_id IS NOT NULL
);

左側の値が NULL になりうるときは2つの方法の結果が違う

PostgreSQL のドキュメントによると、NOT IN の左側が NULL なら結果は NULL です。そのため、id が NULL の行は方法2では外れます。NOT EXISTS では b.user_id = NULL の比較がどの行でも真にならないので、サブクエリは行を返さず、その行は残ります。

上の例の users テーブルでは、この違いを再現できません。SQLite のドキュメントによると、通常のテーブルの INTEGER PRIMARY KEY 列は NULL を保持できず、この列に NULL を挿入すると SQLite が整数値を自動で選んで入れます。そこで NULL を許すテーブルを別に作り、上の blocked テーブルをそのまま使って実行しました。

CREATE TABLE nullable_users(id INTEGER, name TEXT);
INSERT INTO nullable_users VALUES (1,'a'), (2,'b'), (NULL,'x');

-- 方法2
SELECT id, name FROM nullable_users
WHERE id NOT IN (
  SELECT user_id FROM blocked
  WHERE user_id IS NOT NULL
);

-- 方法1
SELECT id, name FROM nullable_users n
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b WHERE b.user_id = n.id
);
クエリ 結果 (id, name)
方法2：NOT IN ＋ IS NOT NULL (1, a)
方法1：NOT EXISTS (1, a), (NULL, x)

どちらが正しいかは要件によります。書き換える前に、左側の列に NULL が入りうるかを確認する必要があります。

参照したドキュメント

リンクと内容は2026年10月4日に確認しました。

ブログへ