はじめに
-
INをEXISTSで書き換えることは、パフォーマンスチューニングのテクニックとしてよく行われます- これは問題ない同値変換です
- しかし、
NOT INをNOT EXISTSで書き換える場合は注意です
実例を紹介していきます
Schema
CREATE TABLE members (
id INT NOT NULL PRIMARY KEY auto_increment,
name TEXT,
age INT,
city TEXT,
class_name TEXT
);
INSERT INTO members VALUES
(1, 'brown', 22, 'tokyo', 'A'),
(2, 'rally', 19, 'saitama', 'A'),
(3, 'boggie', 21, 'chiba', 'A'),
(4, 'saito', 22, 'tokyo', 'B'),
(5, 'tashiro', NULL, 'tokyo', 'B'),
(6, 'yamada', 18, 'tokyo', 'B'),
(7, 'izumi', 19, 'chiba', 'B'),
(8, 'takeda', 20, 'chiba', 'B'),
(9, 'ishikawa', 19, 'kanagawa', 'B');
id = 5 の age が NULL であることに注目です。
Query
抽出したい条件
「東京住まいのクラスBの年齢郡に含まれないクラスAのメンバー」
書き方はいろいろありますが👇
WITH
members_A AS ( SELECT * FROM members WHERE class_name = 'A')
SELECT * FROM members_A
WHERE age NOT IN (SELECT age FROM members WHERE class_name = 'B' AND city = 'tokyo')
期待する Results
NOT IN のカッコの中は (22, null, 18)なので、カッコ外の年齢が選ばれると思うと
以下の結果を期待しますよね？
| id | name | age | city | class_name |
|---|---|---|---|---|
| 2 | rally | 19 | saitama | A |
| 3 | boggie | 21 | chiba | A |
でも、実際は違っていました。
実際の Results
は、1行も選択されません。
順を追って説明します。
1 サブクエリを実行して、年齢のリストを取得
SELECT *
FROM members_A
WHERE age NOT IN (22,NULL,18)
2 NOT IN を NOT と IN を使って同値変換
SELECT *
FROM members_A
WHERE NOT age IN (22,NULL,18)
3 IN述語をORで同値変換
SELECT *
FROM members_A
WHERE NOT ( (age=22) OR (age=NULL) OR (age=18) )
4 ドモルガンの法則を使って同値変換
SELECT *
FROM members_A
WHERE NOT ( (age=22) AND NOT (age=NULL) AND NOT (age=18) )
5 NOT と = を <> で同値変換
SELECT *
FROM members_A
WHERE NOT ( (age<>22) AND (age<>NULL) AND (age<>18) )
6 NULL に <> を適応すると unknownになる
根拠
SELECT *
FROM members_A
WHERE NOT ( (age<>22) AND (unknown) AND (age<>18) )
7 ANDの演算にunknownが含まれるとtrueにはならない
根拠
SELECT *
FROM members_A
WHERE false または unknown
結果として、１行もtrueにはなりません（仕事でこれにハマったら、時間溶かしますよね）。
なので、NOT IN の サブクエリ内にNULLが存在する場合、SQL全体の結果は常に空になります。
恐ろしいですね。。
期待する Resultsが得られる方法は次のようなクエリです。
WITH
members_A AS ( SELECT * FROM members WHERE class_name = 'A')
SELECT * FROM members_A
WHERE NOT EXISTS (
SELECT age
FROM members AS members_B
WHERE class_name = 'B'
AND city = 'tokyo'
AND members_B.age = members_A.age
)
こちらも、年齢がNULLの時の行（id=5）の評価プロセスを段階的に追っていきます。
SELECT * FROM members_A
WHERE NOT EXISTS (
SELECT age
FROM members AS members_B
WHERE class_name = 'B'
AND city = 'tokyo'
AND NULL = members_A.age
)
NULL に = を適応すると unknownになる
→ ANDの演算にunknownが含まれるとtrueにはならない
SELECT * FROM members_A
WHERE NOT EXISTS (
SELECT age
FROM members AS members_B
WHERE class_name = 'B'
AND city = 'tokyo'
AND unknown
)
EXISTS述語は、真偽値しか返しません。
EXISTS句は存在検査ともいわれ，副問合せによって返されたレコードが一つでもあれば真，一つもなければ偽を返します．
→ falseか、unknownが返ってくるけど、EXISTS述語は、真偽値しか返さないので、falseが返ってくる
→ NOT false で、最終的にtrueが返ってくる
SELECT * FROM members_A
WHERE true
最終的な結果が、以下。
| id | name | age | city | class_name |
|---|---|---|---|---|
| 2 | rally | 19 | saitama | A |
| 3 | boggie | 21 | chiba | A |
INとEXISTSは同値変換が可能なのに、NOT IN と NOT EXISTSは同値ではない
この現象は、ちゃんと覚えておきましょう。
参照
69 - 72p
アウトプット100本ノック実施中