はじめに

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本ノック実施中