【SQL】NOT IN と NOT EXISTS の違い

# はじめに

SQL を集中的に勉強していたところ以下のことを知りました。
`IN` と `EXISTS` は置き換え可能だが、`NOT IN` と `NOT EXISTS` を置き換えると期待する結果が得られない場合がある。
それは比較するデータに`NULL`が存在する場合です。

そこで、本記事ではその違いについて紹介したいと思います。

# SQL における真理値について

`NOT IN` と `NOT EXISTS` の違いを捉えるためには、まず SQL の真理値を押さえておく必要があります。
JavaScript などの言語では、比較演算の結果として得られる真理値は `true` または `false` の 2 値です。
しかし SQL は `true` と `false` に加えて、`unknown` が存在します。（3 値論理)
３ 値論理では `NOT` 演算、`AND` 演算そして `OR` 演算では以下のような結果が得られます。

**NOT演算**
||NOT|
|--|--|
|**true**|*false*|
|**unknown**|*unknown*|
|**false**|*true*|

**AND演算**
`false > unknown > true`
||true|false|unknown|
|--|--|--|--|
|**true**|*true*|*false*|*unknown*|
|**false**|*false*|*false*|*false*|
|**unknown**|*unknwon*|*false*|*unknown*|

**OR演算**
`true > unknown > false`
||true|false|unknown|
|--|--|--|--|
|**true**|*true*|*true*|*true*|
|**false**|*true*|*false*|*unknown*|
|**unknown**|*true*|*unknown*|*unknown*|

# `NOT IN`と`NOT EXISTS`の違い

SQLの真理値について押さえることができたので、本題の`NOT IN`と`NOT EXISTS`の違いに入ります。

## テーブルとデータ

ここではタイトル (`title`) と購入回数 (`num_of_purchase`) のデータを持つ books テーブルと albums テーブルを用いて説明します。
それぞれのテーブルのデータは以下に示す通りです。

**booksテーブル**
|id|title|num_of_purchase|
|--|--|--|
|  1 | 羅生門          |              5 |
|  2 | 雪国            |              10 |
|  3 | 坊っちゃん      |              15 |
|  4 | 杳子            |               20 |

**albumsテーブル**
| id | title                               | num_of_purchase |
|----|-------------------------------------|-----------------|
|  1 | サーフ ブンガク カマクラ            |               5 |
|  2 | GOOD POP                            |            NULL |
|  3 | 素晴らしい日々                      |              5 |
|  4 | 世田谷夜明け前                      |               10 |

<details>
  <summary>テーブル・データ作成スクリプト</summary>

```sql
CREATE TABLE books (
  id              INTEGER   NOT NULL AUTO_INCREMENT,
  title           CHAR(32)  NOT NULL UNIQUE,
  num_of_purchase INTEGER,

  PRIMARY KEY (id)
);

INSERT INTO books (title, num_of_purchase)
  VALUES ('羅生門', 5), ('雪国', 10), ('坊っちゃん', 15), ('杳子', 20);


CREATE TABLE albums (
  id              INTEGER   NOT NULL AUTO_INCREMENT,
  title           CHAR(32)  NOT NULL UNIQUE,
  num_of_purchase INTEGER,

  PRIMARY KEY (id)
);

INSERT INTO albums (title, num_of_purchase)
  VALUES ('サーフ ブンガク カマクラ', 5), ('GOOD POP', NULL), ('素晴らしい日々', 5), ('世田谷夜明け前', 10);
```
</details>

## `NOT IN`、`NOT EXISTS` を使用した命令
それでは、`NOT IN` と `NOT EXISTS` を使用して、
*アルバムの購入回数と等しくない購入回数である本のタイトル*
を取得する命令を考えます。

```sql:NOT IN
SELECT title FROM books
    WHERE num_of_purchase NOT IN (
        SELECT num_of_purchase FROM albums
    );
```

```sql:NOT EXISTS
SELECT books.title FROM books
    WHERE NOT EXISTS (
        SELECT * FROM albums
            WHERE books.num_of_purchase = albums.num_of_purchase
    );
```

`NOT IN` を使用すると、結果は Empty set です。
一方で `NOT EXISTS` を使用すると「坊っちゃん」と「杳子」が得られます。
期待するデータが得られるのは `NOT EXISTS` を使用した時の方です。

`NOT IN` の場合に結果が Empty set になるのはなぜか。
その理由を端的に言うと
**`NULL` 値をとるデータを比較演算する場合 `unknown` が返却される**
からです。
`NOT IN` と `NOT EXISTS` のそれぞれの取得過程を見ていくと、具体的に見て取れると思います。

## なぜ `NOT IN` で得られる結果は Empty set なのか？

以下では、一番最初に先ほど実行したスクリプトを示します。
そして、そのスクリプトをよしなに変換しながら `NOT IN` がどのような処理をしているのかを追っていきます。

```sql:NOT IN
-- 実際のSQLスクリプト
SELECT title FROM books
    WHERE num_of_purchase NOT IN (
        SELECT num_of_purchase FROM albums
    );

-- 1. サブクエリの結果　albums テーブルの購入回数が得られる
SELECT title FROM books
    WHERE num_of_purchase NOT IN (5, NULL, 5, 10);
-- 2. NOT IN を NOT と　IN に変換
SELECT title FROM books
    WHERE NOT num_of_purchase IN (5, NULL, 5, 10);
-- 3. IN を OR で表す
SELECT title FROM books
    WHERE NOT (
        (num_of_purchase = 5) OR
        (num_of_purchase = NULL) OR
        (num_of_purchase = 5) OR
        (num_of_purchase = 10)
    );
-- 4. NULL で比較する　(num_of_purchase = NULL)　と結果は unknown になる
SELECT title FROM books
    WHERE NOT (
        (num_of_purchase = 5) OR
        unknown               OR
        (num_of_purchase = 5) OR
        (num_of_purchase = 10)
    );
-- 5. ド・モルガンの法則を用いて AND を使用する
SELECT title FROM books
    WHERE
        NOT (num_of_purchase = 5) AND
        NOT unknown               AND
        NOT (num_of_purchase = 5) AND
        NOT (num_of_purchase = 10)
    );
-- 6. unknown の否定は unknown　(NOT 演算の真理値表を参照)
SELECT title FROM books
    WHERE
        NOT (num_of_purchase = 5) AND
            unknown               AND
        NOT (num_of_purchase = 5) AND
        NOT (num_of_purchase = 10)
    );
-- 7. unknown を含む　AND 演算は結果が false または unknown　(AND　演算の真理値表を参照)
SELECT title FROM books
    WHERE false または unknown
```

以上の過程から、`NULL`を含むカラムを用いる場合`NOT IN`では必ずクエリの結果が`false`または`unknown`になります。
そのため、最終的に得られる結果がEmpty setになります。

## なぜ`NOT EXISTS`では期待する結果が得られるのか？

`NOT IN`の時と同様にして、スクリプトをよしなに変換しながら、`NOT EXISTS`がどのような処理をしているのかを追っていきます。

```sql:NOT EXISTS
-- 実際のSQLスクリプト
SELECT books.title FROM books
    WHERE NOT EXISTS (
        SELECT * FROM albums
            WHERE books.num_of_purchase = albums.num_of_purchase
    );

-- 1. サブクエリで NULL の比較を行う
SELECT books.title FROM books
    WHERE NOT EXISTS (
        SELECT * FROM albums
            WHERE books.num_of_purchase = NULL
    );
-- 2. 結果 unknown　が得られる
SELECT books.title FROM books
    WHERE NOT EXISTS (
        SELECT * FROM albums
            WHERE unknown
    );
-- 3. サブクエリは結果を返さないため NOT EXISTS は true
SELECT books.title FROM books
    WHERE true
```

以上の過程から、authors テーブルにおいて `num_of_purchase` が `NULL` であるデータでは、books テーブルの全てのデータが結果として得られます。
また、`EXISTS` はクエリをもとにしたデータの集合を作成します。
つまり、以上の過程ではalbumsテーブルの `num_of_purchase` が `NULL` であるデータと books テーブルのデータを比較していましたが、他の albums テーブルのデータも比較します。
その結果として、タイトルが「坊っちゃん」と「杳子」が得られます。

# おわりに

`IN` と `EXISTS` は置き換え可能であるにも関わらず、`NOT IN` と `NOT EXISTS` は置き換えられないケースがあると言うのはハマるポイントに違いありません。
忘れないでおきたいです。