はじめに
パフォーマンスチューニングの手法の一つとして、サブクエリ＆INの組み合わせをEXISTSに置き換える方法があります。
何も知らなかった僕はそのままNOT INもNOT EXISTSに置き換えれるものだろうと思っていたのですが、ここに罠があったなんて...
結論から言うと nullable なカラムを対象とした場合、NOT IN と NOT EXISTSで挙動が異なります。
INをEXISTSに変換する
まずINをEXISTSに変換してみましょう。
> select * from member;
+----+----------+------+---------------+
| id | name | age | prefecture_id |
+----+----------+------+---------------+
| 1 | Tanaka | 22 | 1 |
| 2 | Morimoto | 21 | NULL |
| 3 | Sato | 18 | 1 |
| 4 | Kimura | 17 | 2 |
| 5 | Shimizu | 20 | 2 |
| 6 | Hukuda | 19 | 3 |
| 7 | Wada | 28 | 4 |
| 8 | Yamamoto | 17 | 5 |
| 9 | Kawasaki | 18 | 5 |
| 10 | Morimoto | 18 | 5 |
| 11 | Hamamoto | 16 | 5 |
+----+----------+------+---------------+
> select * from prefecture;
+----+----------+--------+
| id | name | region |
+----+----------+--------+
| 1 | Tokyo | Kanto |
| 2 | Kanagawa | Kanto |
| 3 | Chiba | Kanto |
| 4 | Osaka | Kinki |
| 5 | Hyogo | Kinki |
+----+----------+--------+
このようなテーブルがあった時に、「memberの中で成人している人がいる都道府県」を抽出するとします。
つまり、
-- prefecture
+----+----------+--------+
| id | name | region |
+----+----------+--------+
| 1 | Tokyo | Kanto |
| 2 | Kanagawa | Kanto |
| 4 | Osaka | Kinki |
+----+----------+--------+
が出ればいいものとします。
20歳以上のメンバーの都道府県IDのリストをサブクエリで取得し、
このリストに存在しない都道府県を出力すれば良いでしょう。
INを使った場合、
SELECT *
FROM prefecture
WHERE id IN
( SELECT prefecture_id
FROM member
WHERE age >= 20 );
こうなりますがEXISTSを用いて
SELECT *
FROM prefecture
WHERE EXISTS
( SELECT prefecture_id
FROM member
WHERE member.age >= 20
AND member.prefecture_id = prefecture.id);
とも書けます。
このようにサブクエリを引数にとるようなINではEXISTSを用いた方が多くの場合で高速です。
nullableなカラムでNOT IN句を使用する時
NOT IN句ではnullなカラムが含まれていると直感と外れる動作をするので注意が必要です。
例えば「memberの中で未成年者しかいない都道府県」を抽出するとします。
つまり、
-- prefecture
+----+----------+--------+
| id | name | region |
+----+----------+--------+
| 3 | Chiba | Kanto |
| 5 | Hyogo | Kinki |
+----+----------+--------+
が出ればいいものとします。
考え方としては、20歳以上のメンバーの都道府県IDのリストをサブクエリで取得し、
このリストに存在しない都道府県を出力すれば良いでしょう。
これをそのままクエリにすると以下のようになります。
SELECT *
FROM prefecture
WHERE id NOT IN
( SELECT prefecture_id
FROM member
WHERE age >= 20 );
つまり、先ほどのクエリのINをNOT INに変えただけですね。
ところがこのクエリの結果はなんと空です。
nullableなカラムでNOT EXISTSを使用する時
では、これをNOT EXISTSで置き換えてみましょう。
こちらも先ほどのクエリにNOTを付けただけとなります。
SELECT *
FROM prefecture
WHERE NOT EXISTS
( SELECT prefecture_id
FROM member
WHERE member.age >= 20
AND member.prefecture_id = prefecture.id);
こちらの結果は意図通りの結果となります。
-- prefecture
+----+----------+--------+
| id | name | region |
+----+----------+--------+
| 3 | Chiba | Kanto |
| 5 | Hyogo | Kinki |
+----+----------+--------+
なぜでしょうか？
ちなみにmember.id = 2のprefecture_idがnullでなければ、両者は同じ結果を返します。
prefecture_idにnullを禁止するとNOT INはNOT EXISTSにそのまま変換できます。
NOT INとNOT EXISTSの内部処理
この違いは内部でどのような変換作業が行われているのかを知ることでなんとなくわかります。
(参考) 達人に学ぶSQL徹底指南書 第2版 ミック著
NOT IN
SELECT *
FROM prefecture
WHERE id NOT IN ( SELECT prefecture_id FROM member WHERE age >= 20 );
=> WHERE id NOT IN (1,null,2,4)
=> WHERE NOT ((id = 1) or (id = null) or (id = 2) or (id = 4))
=> WHERE id NOT (id = 1) AND NOT (id = null) AND NOT (id = 2) AND NOT (id = 4) -- ド・モルガンの法則
=> WHERE (id <> 1) AND (id <> null) AND (id <> 2) AND (id <> 4)
=> WHERE (id <> 1) AND unknown AND (id <> 2) AND (id <> 4)
=> WHERE unknown
となり、WHERE句は常にtrueではなくなります。
idに何が入ろうが、unknownがANDでつながっている時点で表示されません。
つまり空の結果が帰ってきます。
論理積和演算のやり方は[SQLの3値論](# [補足]SQLの3値論)をご覧ください。
NOT EXISTS
prefecture_id = 1の行が表示されるか否かは、
SELECT *
FROM prefecture
WHERE NOT EXISTS ( SELECT prefecture_id FROM member WHERE member.age >= 20 AND member.prefecture_id = prefecture.id);
-- member.id = 1
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE 22 >= 20 AND 1 = 1);
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE true AND true);
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE true); -- member.id=1のユーザのprefecture.idが帰ってくる
=> NOT true;
=> false;
-- member.id = 2
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE member.age >= 20 AND null = 1);
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE true AND unknown);
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE unknown);
=> NOT false; -- サブクエリのWHERE unknownの部分で何も返してこない
=> true;
...
-- 全員分やってANDで繋ぐとfalseなので表示されない。
prefecture.id = 3の行が表示されるか否かは、
SELECT *
FROM prefecture
WHERE NOT EXISTS ( SELECT prefecture_id FROM member WHERE member.age >= 20 AND member.prefecture_id = prefecture.id);
-- member.id = 1
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE 22 >= 20 AND 1 = 3);
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE true AND false);
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE false); -- 何も帰ってこない
=> NOT false;
=> true;
-- member.id = 2
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE member.age >= 20 AND null = 3);
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE true AND unknown);
=> NOT EXISTS ( SELECT prefecture_id FROM member WHERE unknown);
=> NOT false; -- サブクエリのWHERE unknownの部分で何も返してこない
=> true;
...
-- 全員分やってANDで繋ぐとtrueなので表示される。
EXISTSの場合は必ずtrueかfalseかが帰ってきます。
内部の処理は難しいですが、結果は直感的なものが帰ってくるし、こちらの方が速いので、なるべくEXISTSを使った方がいいのかなと思います。
終わりに
本当にNULL悪さしかしない。。。
[補足]SQLの３値論
例えば「memberの中でprefecture_id = 1でない かつ 成人している人」を抽出するとします。
SELECT *
FROM member
WHERE prefecture_id <> 1 AND age >= 20;
結果はこのようになります。
prefecture_id = NULLのユーザは結果にありません。
+----+---------+------+---------------+
| id | name | age | prefecture_id |
+----+---------+------+---------------+
| 5 | Shimizu | 20 | 2 |
| 7 | Wada | 28 | 4 |
+----+---------+------+---------------+
なぜでしょうか？
実はmember.id = 2のユーザは内部ではこのように評価されています。
prefecture_id <> 1 AND age >= 20
=> NULL <> 1 AND 21 >= 20
=> unknown AND true
=> unknown
このようにnullを含む式は全てunknownとして評価されます。
-- 全部 unknown
null = 1
null = null
null < 1
1 < null
1 <> null
...
またSQLの世界で真理値は以下のようになります。
- 否定
| NOT | true | unknown | false |
|---|---|---|---|
| false | unknown | true |
- 論理和
| OR | true | unknown | false |
|---|---|---|---|
| true | true | unknown | false |
| unknown | unknown | unknown | unknown |
| false | false | unknown | false |
- 論理積
| AND | true | unknown | false |
|---|---|---|---|
| true | true | unknown | false |
| unknown | unknown | unknown | unknown |
| false | false | unknown | false |
そしてWHEREで選択されるレコードはtrueとなるもののみです。
なのでunknownとfalseに評価される行は選択されません。