「注文したことのない会員」を NOT IN で探していたのに、ある日から結果が0件になりました。原因は、サブクエリの結果に NULL が1件だけ混ざったことです。NOT IN は、比べる相手に NULL が1つでもあると、どの行も条件に合うと判定できなくなります。
ここでは、MySQL 8.0 で実際に0件になる様子を見てから、三値論理(TRUE・FALSE・UNKNOWN の3つで判定するしくみ)で理由を確かめます。直し方は NOT EXISTS・LEFT JOIN・IS NOT NULL の3通りです。外側の列が NULL のときに、直し方によって結果が変わる点も扱います。
以下の結果は MySQL 8.0 で実際に実行したものです。
このシナリオで考える
会員テーブル customers と、注文テーブル orders を使います。このお店では、会員登録をしないで買うゲスト購入ができます。ゲスト購入の注文は customer_id が NULL になる作りです。
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NULL,
amount INT NOT NULL
);
INSERT INTO customers VALUES
(1, '佐藤'),
(2, '鈴木'),
(3, '高橋'),
(4, '田中');
INSERT INTO orders VALUES
(101, 1, 3000),
(102, 3, 1500),
(103, 1, 2000);
佐藤さんと高橋さんは注文があり、鈴木さんと田中さんはまだ一度も注文していません。この2人にクーポンを送るために、注文のない会員を取り出したいとします。
ゲスト購入が1件入ると NOT IN が0件になる
最初は NOT IN で問題なく取れます。注文テーブルの customer_id の一覧に入っていない会員を返すクエリです。
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
+-------------+--------+
| customer_id | name |
+-------------+--------+
| 2 | 鈴木 |
| 4 | 田中 |
+-------------+--------+
ここでゲスト購入の注文が1件入ります。customer_id は NULL です。
INSERT INTO orders VALUES (104, NULL, 800);
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
(0件。1行も返りません)
同じクエリなのに、鈴木さんも田中さんも消えました。エラーも警告も出ません。800円の注文が1件増えただけで、クーポンの送り先が0人になります。
同じサブクエリを IN にすると、こちらは NULL が入っても結果は変わりません。
SELECT customer_id, name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
+-------------+--------+
| customer_id | name |
+-------------+--------+
| 1 | 佐藤 |
| 3 | 高橋 |
+-------------+--------+
IN は正しく動き、NOT IN だけが0件になります。この違いは、次の三値論理で説明できます。
三値論理で見る NOT IN の中身
NOT IN は、相手の値1つ1つと「等しくない」を比べ、それを AND でつないだものと同じです。2 NOT IN (1, 3, NULL) は 2 <> 1 AND 2 <> 3 AND 2 <> NULL になります。
NULL との比較は、TRUE でも FALSE でもない UNKNOWN(MySQL では NULL と表示)になります。TRUE AND UNKNOWN は UNKNOWN です。WHERE 句は TRUE の行だけを残すので、UNKNOWN の行は落とされます。
4つの式を1行で確かめます。
SELECT
2 NOT IN (1, 3) AS a,
2 NOT IN (1, 3, NULL) AS b,
1 NOT IN (1, 3, NULL) AS c,
2 IN (1, 3, NULL) AS d;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | NULL | 0 | NULL |
+---+------+------+------+
| 式 | 結果 | WHERE に書いたとき | 理由 |
|---|---|---|---|
| a: 2 NOT IN (1, 3) | 1(TRUE) | 残る | どの値とも等しくない |
| b: 2 NOT IN (1, 3, NULL) | NULL(UNKNOWN) | 落ちる | 2 <> NULL が UNKNOWN |
| c: 1 NOT IN (1, 3, NULL) | 0(FALSE) | 落ちる | 1 <> 1 が FALSE なので、AND 全体が FALSE |
| d: 2 IN (1, 3, NULL) | NULL(UNKNOWN) | 落ちる | 一致がないので落ちるのは正しい |
NOT IN の相手に NULL が1つでもあると、結果は FALSE か UNKNOWN のどちらかになります。TRUE になる行がなくなるので、0件です。
IN が壊れないのは、OR でつながっているからです。一致する値が1つあれば TRUE になり、NULL があっても結果は変わりません。一致しない行は UNKNOWN で落ちますが、もともと落としたい行なので困りません。
NOT EXISTS・LEFT JOIN・IS NOT NULL での直し方
一番おすすめなのは NOT EXISTS です。サブクエリが1行も返さないときだけ TRUE になり、NULL の行は o.customer_id = c.customer_id に一致しないので、結果に影響しません。
SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
+-------------+--------+
| customer_id | name |
+-------------+--------+
| 2 | 鈴木 |
| 4 | 田中 |
+-------------+--------+
LEFT JOIN で結合し、相手が見つからなかった行だけを残す書き方でも同じ結果になります。IS NULL を調べる列は、注文テーブルの主キー order_id のように NULL になりえない列にします。
SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
+-------------+--------+
| customer_id | name |
+-------------+--------+
| 2 | 鈴木 |
| 4 | 田中 |
+-------------+--------+
NOT IN のまま直すなら、サブクエリで NULL を除きます。既存のクエリを少しだけ変えたいときに向いています。
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);
+-------------+--------+
| customer_id | name |
+-------------+--------+
| 2 | 鈴木 |
| 4 | 田中 |
+-------------+--------+
| 書き方 | 相手の NULL | 外側の列の NULL(次の見出し) | 向いている場面 |
|---|---|---|---|
| NOT EXISTS | 影響しない | 結果に残る | 新しく書くとき。まずはこれ |
| LEFT JOIN … IS NULL | 影響しない | 結果に残る | 相手のテーブルの列も一緒に見たいとき |
| NOT IN + IS NOT NULL | 除いているので影響しない | 結果から消える | 既存の NOT IN を最小の変更で直すとき |
片方のテーブルにしかない行を取る書き方は「SQLで片方のテーブルにしか存在しないデータを抽出する3つの方法【LEFT JOIN・NOT EXISTS・EXCEPT】」で比べています。
外側の列が NULL のときは結果が分かれる
3つの直し方は、外側のテーブルの列が NULL の行で結果が変わります。会員に配ったクーポンの表で確かめます。C300 はまだ誰にも渡していない共通クーポンで、customer_id が NULL です。
CREATE TABLE coupons (
coupon_code VARCHAR(10) PRIMARY KEY,
customer_id INT NULL
);
INSERT INTO coupons VALUES
('A100', 1),
('B200', 2),
('C300', NULL);
-- NOT IN(相手の NULL は除いてある)
SELECT coupon_code, customer_id
FROM coupons
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);
+-------------+-------------+
| coupon_code | customer_id |
+-------------+-------------+
| B200 | 2 |
+-------------+-------------+
-- NOT EXISTS
SELECT cp.coupon_code, cp.customer_id
FROM coupons AS cp
WHERE NOT EXISTS (
SELECT 1 FROM orders AS o WHERE o.customer_id = cp.customer_id
);
+-------------+-------------+
| coupon_code | customer_id |
+-------------+-------------+
| B200 | 2 |
| C300 | NULL |
+-------------+-------------+
NOT IN では、NULL NOT IN (1, 3) が UNKNOWN になり、C300 が落ちます。NOT EXISTS では、NULL と一致する注文が見つからないので、C300 は残ります。
どちらが正しいかは、何を知りたいかで決まります。「注文のない会員に渡したクーポン」だけが欲しいなら、NOT EXISTS に AND cp.customer_id IS NOT NULL を足して、持ち主のいないクーポンをはっきり外してください。条件を書いておくと、あとで読む人にも意図が伝わります。
サブクエリが0件のときは、また別の結果になります。比べる相手が空なら、外側が NULL でも NOT IN は TRUE を返します。
SELECT
NULL NOT IN (SELECT customer_id FROM orders WHERE amount > 99999) AS empty_case,
NULL NOT IN (1, 3) AS non_empty_case;
+------------+----------------+
| empty_case | non_empty_case |
+------------+----------------+
| 1 | NULL |
+------------+----------------+
データの件数しだいで結果が変わるので、外側の列に NULL がありうるときは NOT IN を避けるのが安全です。
よくある落とし穴
- テストでは気づけない。開発中のデータに NULL がなければ、NOT IN は正しく動きます。本番で NULL の行が1件入った日から、急に0件になります。
- NOT NULL 制約がない列をサブクエリで使う。サブクエリの列が NOT NULL なら NOT IN でも安全です。NULL を許す列なら、今は NULL がなくても NOT EXISTS にしておくと、あとで壊れません。
- アプリで作った値の一覧に NULL が混ざる。
NOT IN (1, 3, NULL)のように、プログラムで組み立てた一覧に空の値が入ったときも同じことが起きます。 - 速さを理由に NOT IN を選ぶ。MySQL 8.0.17 以降は、NOT IN と NOT EXISTS のサブクエリをどちらもアンチ結合(相手に一致しない行だけを残す結合)に書きかえて実行できます。速さのために NOT IN にこだわる理由はありません。
IN と EXISTS の動きの違いや EXPLAIN での見え方は「SQLのEXISTSとINの違いと使い分け:NULL問題と性能まとめ」にまとめています。
まとめ
NOT IN は「等しくない」を AND でつないだものなので、相手に NULL が1つあると TRUE になる行がなくなり、0件になります。新しく書くなら NOT EXISTS、既存の NOT IN を直すならサブクエリに IS NOT NULL を足してください。外側の列が NULL の行は、書き方によって残ったり消えたりします。残したくないときは IS NOT NULL の条件をはっきり書いておきます。
もっと学ぶなら【PR】
- 達人に学ぶSQL徹底指南書 第2版:CASE 式やウィンドウ関数など、一歩進んだ書き方がわかる中級者向けの本
- SQLアンチパターン 第2版:テーブル設計やクエリでやりがちな失敗と、その直し方をまとめた本
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 Reference Manual: Comparison Functions and Operators(IN・NOT IN)
- MySQL 8.0 Reference Manual: Subqueries with ANY, IN, or SOME
- MySQL 8.0 Reference Manual: Subqueries with EXISTS or NOT EXISTS
- MySQL 8.0 Reference Manual: Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations
