[SQL]単純にNOT INをNOT EXISTSに置き換えることはできない

# はじめに
パフォーマンスチューニングの手法の一つとして、サブクエリ＆INの組み合わせをEXISTSに置き換える方法があります。
何も知らなかった僕はそのまま`NOT IN`も`NOT EXISTS`に置き換えれるものだろうと思っていたのですが、ここに罠があったなんて...
結論から言うと *nullable* なカラムを対象とした場合、`NOT IN` と `NOT EXISTS`で挙動が異なります。

# INをEXISTSに変換する
まずINをEXISTSに変換してみましょう。

```sql

> 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の中で成人している人がいる都道府県」を抽出するとします。
つまり、

```sql

-- prefecture
+----+----------+--------+
| id | name     | region |
+----+----------+--------+
|  1 | Tokyo    | Kanto  |
|  2 | Kanagawa | Kanto  |
|  4 | Osaka    | Kinki  |
+----+----------+--------+
```
が出ればいいものとします。
20歳以上のメンバーの都道府県IDのリストをサブクエリで取得し、
このリストに存在しない都道府県を出力すれば良いでしょう。
INを使った場合、

```sql 
SELECT * 
  FROM prefecture
  WHERE id IN 
      ( SELECT prefecture_id 
          FROM member
          WHERE age >= 20 );
```

こうなりますがEXISTSを用いて

```sql 
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の中で未成年者しかいない都道府県」を抽出するとします。
つまり、

```sql

-- prefecture
+----+----------+--------+
| id | name     | region |
+----+----------+--------+
|  3 | Chiba    | Kanto  |
|  5 | Hyogo    | Kinki  |
+----+----------+--------+
```
が出ればいいものとします。
考え方としては、20歳以上のメンバーの都道府県IDのリストをサブクエリで取得し、
このリストに存在しない都道府県を出力すれば良いでしょう。
これをそのままクエリにすると以下のようになります。

```sql 
SELECT * 
  FROM prefecture
  WHERE id NOT IN 
      ( SELECT prefecture_id 
          FROM member
          WHERE age >= 20 );
```
つまり、先ほどのクエリの`IN`を`NOT IN`に変えただけですね。
ところがこのクエリの結果はなんと空です。
# nullableなカラムでNOT EXISTSを使用する時
では、これをNOT EXISTSで置き換えてみましょう。
こちらも先ほどのクエリに`NOT`を付けただけとなります。

```SQL
SELECT * 
  FROM prefecture
  WHERE NOT EXISTS 
      ( SELECT prefecture_id 
          FROM member
          WHERE member.age >= 20
          AND member.prefecture_id = prefecture.id);
```
こちらの結果は意図通りの結果となります。

```sql

-- 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版　ミック著](https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E7%AC%AC2%E7%89%88-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-CodeZine-BOOKS/dp/4798157821)
## NOT IN

```sql
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`の行が表示されるか否かは、

```sql 
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`の行が表示されるか否かは、

``` sql 
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でない　かつ　成人している人」を抽出するとします。

```sql

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のユーザは内部ではこのように評価されています。

```sql
   prefecture_id <> 1 AND age >= 20
=> NULL <> 1 AND 21 >= 20 
=> unknown AND true
=> unknown 
```

このように`null`を含む式は全て`unknown`として評価されます。

```sql
-- 全部 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`に評価される行は選択されません。

 
# 参考文献
[達人に学ぶSQL徹底指南書 第2版　ミック著](https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E7%AC%AC2%E7%89%88-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-CodeZine-BOOKS/dp/4798157821)