NOT IN の サブクエリ内にNULLが存在する場合、SQL全体の結果は常に空なワケ

## はじめに

- `IN`を`EXISTS`で書き換えることは、パフォーマンスチューニングのテクニックとしてよく行われます
    - これは問題ない同値変換です
- しかし、`NOT IN`を`NOT EXISTS`で書き換える場合は注意です

実例を紹介していきます

## Schema

```sql
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のメンバー」

書き方はいろいろありますが👇

```sql
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 サブクエリを実行して、年齢のリストを取得

```sql
SELECT *
FROM members_A
WHERE age NOT IN (22,NULL,18)
```

2 NOT IN を NOT と IN を使って同値変換

```sql
SELECT *
FROM members_A
WHERE NOT age IN (22,NULL,18)
```

3 IN述語をORで同値変換

```sql
SELECT *
FROM members_A
WHERE NOT ( (age=22) OR (age=NULL) OR (age=18) )
```

4 ドモルガンの法則を使って同値変換

```sql
SELECT *
FROM members_A
WHERE NOT ( (age=22) AND NOT (age=NULL) AND NOT (age=18) )
```

5 NOT と = を <> で同値変換

```sql
SELECT *
FROM members_A
WHERE NOT ( (age<>22) AND (age<>NULL) AND (age<>18) )
```

6 NULL に <> を適応すると unknownになる

根拠

https://qiita.com/ryosuketter/items/55d418f808d318a0802a

```sql
SELECT *
FROM members_A
WHERE NOT ( (age<>22) AND (unknown) AND (age<>18) )
```

7 ANDの演算にunknownが含まれるとtrueにはならない

根拠

https://qiita.com/ryosuketter/items/e9eadaeb48540e94d88b

```sql
SELECT *
FROM members_A
WHERE false または unknown
```

結果として、１行もtrueにはなりません（仕事でこれにハマったら、時間溶かしますよね）。

なので、**NOT IN の サブクエリ内にNULLが存在する場合、SQL全体の結果は常に空**になります。

恐ろしいですね。。:scream:

期待する Resultsが得られる方法は次のようなクエリです。

```sql
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）の評価プロセスを段階的に追っていきます。

```sql
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にはならない

```sql
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句は存在検査ともいわれ，副問合せによって返されたレコードが一つでもあれば真，一つもなければ偽を返します．

https://www.sql-reference.com/select/subquery_exists.html


→ falseか、unknownが返ってくるけど、EXISTS述語は、真偽値しか返さないので、falseが返ってくる
→ NOT false で、最終的にtrueが返ってくる

```sql
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は同値ではない**

この現象は、ちゃんと覚えておきましょう。

## 参照

https://www.amazon.co.jp/dp/4798157821/

69 - 72p 

## アウトプット100本ノック実施中

https://qiita.com/ryosuketter/items/713474dbd009870e4103