【SQL】そのNOT IN の使い方危険です！

# はじめに
SQLを使ってデータベースからデータを取得する際、特定の条件に合致しないレコードを抽出したい場面があります。そのような場合、NOT INやNOT EXISTSを使用することが一般的です。一見、この2つの演算子は同じような結果を返すように見えますが、実はNULLの扱いに関して大きな違いがあります。


※本記事では、NOT INとNOT EXISTSの動作の違いについて詳しく説明し、NULLが存在する場合の問題を解決する方法としてNOT EXISTSを使用することを提案します。なお、NOT INをIS NOT NULLと組み合わせる方法については、本記事では詳しく説明しません。

# テーブルの準備
今回使用するサンプルデータは下記になります。
<details><summary>SQL</summary>

```SQL
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (id, name, email)
VALUES
(1, 'test1', 'hoge1@example.com'),
(2, 'test2', 'hoge2@example.com'),
(3, 'test3', 'hoge3@example.com'),
(4, 'test4', 'hoge4@example.com'),
(5, 'test5', 'hoge5@example.com');

INSERT INTO orders (id, customer_id, order_date, total_amount)
VALUES
(1, 1, '2023-04-01', 5000),
(2, 1, '2023-04-15', 3000),
(3, 2, '2023-04-10', 8000),
(4, 3, '2023-04-20', 6000),
(5, 4, '2023-04-25', 10000),
(6, NULL, '2023-04-30', 7000);
```
</details>


顧客情報のテーブル
| id  | name  |       email       |
| :-: | :---: | :---------------: |
|  1  | test1 | hoge1@example.com |
|  2  | test2 | hoge2@example.com |
|  3  | test3 | hoge3@example.com |
|  4  | test4 | hoge4@example.com |
|  5  | test5 | hoge5@example.com |

注文情報のテーブル
| id  | customer_id | order_date | total_amount |
| :-: | :---------: | :--------: | :----------: |
|  1  |      1      | 2023-04-01 |   5000.00    |
|  2  |      1      | 2023-04-15 |   3000.00    |
|  3  |      2      | 2023-04-10 |   8000.00    |
|  4  |      3      | 2023-04-20 |   6000.00    |
|  5  |      4      | 2023-04-25 |   10000.00   |
|  6  |    NULL     | 2023-04-30 |   7000.00    |

# 要件
本記事では、一度も注文したことのない顧客を検索するという要件を使って、NOT INとNOT EXISTSの動作の違いを確認していきます。

この要件は、顧客テーブルとその顧客の注文情報を含む注文テーブルを使用する、非常によくある状況です。

本記事では、実際にNOT INとNOT EXISTSを使ってクエリを実行し、その結果を比較していきます。


# NOT IN を使用した場合
まず、NOT INを使って一度も注文したことのない顧客を検索してみます。

期待している結果は、注文情報テーブル（orders）のcustomer_idに存在しない顧客、
つまりtest5さんを取得することです。

以下のSQLを実行してみます。
```SQL
SELECT
    id,
    name
FROM customers
WHERE id NOT IN ( SELECT customer_id FROM orders );
```

一見、このSQLは正しく動作しそうですが、実際に実行結果を確認すると...
|id|name|
|:--:|:--:|
|||

びっくり！！なんと空が返ってきます！
実は、この予期せぬ動作の原因は、ordersテーブルに存在するNULLの値にあります。
ordersテーブルを見返してみると、最後の行のcustomer_idがNULLになっていることに気づきます。

NOT INは、このNULLの存在によって期待通りの動作をしなくなってしまうのです。

## NOT INの動作の解説

前のセクションで見たように、NOT INはNULLの存在によって期待通りの動作をしなくなります。
この理由を理解するには、SQLにおけるNULLの性質と、真理値の優先順位について知る必要があります。

### NULLの性質
NULLは、SQLにおいて「値が存在しない」ことを表すための特別なマーカーです。これは、未知の値や適用不可能な値を表現するために使用されます。NULLは値ではないため、他の値と同じように扱うことはできません。

比較演算子（=, <>, <, >, <=, >=）を使ってNULLを比較しようとすると、常にUNKNOWNが返されます。これは、NULLが不明であるため、比較結果も不明になるためです。

例えば、以下のような比較をしてみましょう。

```SQL
1 = NULL;    -- 結果: UNKNOWN
1 <> NULL;   -- 結果: UNKNOWN
1 > NULL;    -- 結果: UNKNOWN
NULL = NULL; -- 結果: UNKNOWN
```

これらの比較は全てUNKNOWNになります。NULLは値ではないため、比較演算子を使ってNULLと値を比較することはできないのです。

### クエリ結果とWHERE句の評価
クエリの結果として選択されるのは、WHERE句の評価条件が`TRUE`になる行です。`FALSE`や`UNKNOWN`の行は選択されません。
つまり、NULLに関する比較演算子の結果が`UNKNOWN`になってしまうと、その行はクエリ結果に含まれなくなってしまうのです。

### 真理値の優先順位
SQLでは、真理値（TRUE, FALSE, UNKNOWN）の間に優先順位が存在します。
この優先順位は、AND演算子とOR演算子で異なります。

- **AND**演算子の場合：FALSE > UNKNOWN > TRUE
- **OR** 演算子の場合：TRUE > UNKNOWN > FALSE

つまり、**AND**演算子では、いずれかのオペランドが`FALSE`であれば結果は`FALSE`になり、
いずれかのオペランドが`UNKNOWN`であれば結果は`UNKNOWN`になります。

一方、**OR**演算子では、いずれかのオペランドが`TRUE`であれば結果は`TRUE`になり、いずれかのオペランドが`UNKNOWN`であれば結果は`UNKNOWN`になります。

## NOT INの動作とNULL
ここまでで、NULLの比較結果と真理値の優先順位について見てきました。
ここからはNOT INがどの様な処理を行っているか見ていきます

### NOT INの評価プロセス
```SQL
-- サブクエリ実行して顧客IDを取得する
SELECT
    id,
    name
FROM customers
WHERE id NOT IN ( 1, 2, 3, 4, NULL);

-- NOT IN をNOTとINに分解する
SELECT
    id,
    name
FROM customers
WHERE NOT id IN( 1, 2, 3, 4, NULL);

-- IN述語をORで同値変換

SELECT
    id,
    name
FROM customers
WHERE NOT ( (id = 1) OR (id = 2) OR (id = 3) OR (id = 4) OR (id = NULL) );

-- ド・モルガンの法則を適用し同値変換
SELECT
    id,
    name
FROM customers
WHERE NOT ( id = 1 ) AND NOT ( id = 2 ) AND NOT ( id = 3 ) AND NOT ( id = 4 ) AND NOT ( id = NULL );

-- NOTと=を<>で同値変換
SELECT
    id,
    name
FROM customers
WHERE (id <> 1) AND (id <> 2) AND (id <> 3) AND (id <> 4) AND (id <> NULL);

-- NULLに<>を適用するとunknownになる
SELECT
    id,
    name
FROM customers
WHERE (id <> 1) AND (id <> 2) AND (id <> 3) AND (id <> 4) AND unknown;

```

ここで、真理値の優先順位が効いてきます。`AND`演算子では、いずれかのオペランドが`UNKNOWN`の場合、結果は`UNKNOWN`になります。

つまり、`NOT IN`の条件全体の結果が`UNKNOWN`になってしまうのです。そして、`WHERE句`では`TRUE`のみが選択されるため、`UNKNOWN`の行は結果に含まれません。

これが、NOT INがNULLの存在によって空の結果を返してしまう理由です。

# NOT EXISTSを使用した解決策

NOT INがNULLの存在によって期待通りの動作をしない問題を解決するには、NOT EXISTSを使用します。
まずは、NOT EXISTSを使用したクエリとその実行結果を見ていきましょう。


```SQL
-- NOT EXISTSを利用した例
SELECT id,name
FROM customers c
WHERE NOT EXISTS (
    SELECT *
    FROM orders o
    WHERE c.id = o.customer_id
);

```
### 実行結果

| id  | name  |
| :-: | :---: |
|  5  | test5 |


期待通りの結果が取得できていますね。

### EXISTSとは
EXISTSは、指定された条件に合致する行が存在するかどうかを確認するSQL演算子です。EXISTSは、サブクエリと一緒に使用されます。

EXISTSの基本的な構文は以下の通りです。
```SQL
SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);
```

サブクエリが少なくとも1行を返す場合、EXISTSは`TRUE`を返します。サブクエリが何も返さない場合、EXISTSは`FALSE`を返します。

重要なのは、EXISTSはサブクエリの結果の内容を考慮しないということです。サブクエリが実際にどのような行を返すかは問題ではありません。EXISTSは、サブクエリが何らかの結果を返すかどうかのみを評価します。

### EXISTSとNULL

EXISTSの重要な特性の1つは、絶対に`UNKNOWN`を返さないことです。EXISTSは`TRUE`か`FALSE`のみを返します。

これは、サブクエリ内のNULL比較の結果がEXISTSの動作に影響を与えないことを意味します。サブクエリ内のNULL比較が`UNKNOWN`を返したとしても、EXISTSはサブクエリが行を返したかどうかのみを考慮します。

この特性により、EXISTSはNULLが関連するクエリで信頼性の高い結果を提供することができます。


### NOT EXISTSの評価プロセス
こちらも評価プロセスを段階的に追ってみます。

```SQL

-- NOT EXISTS を使用した場合
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT *
    FROM orders o
    WHERE c.id = o.customer_id
);

-- サブクエリでNULL比較を行う
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT *
    FROM orders o
    WHERE c.id = NULL
);

-- NULLに=を適用するとunknownになる
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT *
    FROM orders o
    WHERE unknown
);

-- サブクエリが結果を返さない場合、反対にNOT EXISTSはTRUEとなる
SELECT *
FROM customers c
WHERE TRUE;

```

以上の過程から、NOT EXISTSがNULLの存在に影響されない理由が明らかになります。

具体的には、以下のようなプロセスが行われています。

1. customersテーブルから1行ずつレコードが取得されます
2. 各レコードに対して、サブクエリが実行されます
    -  最初の4行（id = 1, 2, 3, 4）の場合、サブクエリはordersテーブルから一致するcustomer_idを持つ行を見つけます。したがって、サブクエリは空ではなくなり、NOT EXISTSはFALSEを返します
    - 5行目（id = 5）の場合、サブクエリはordersテーブルから一致するcustomer_idを持つ行を見つけられません。5 = NULLの比較はUNKNOWNを返しますが、これによってサブクエリが空になることはありません。サブクエリは単に何も返さないだけです。したがって、NOT EXISTSはTRUEを返します

3. NOT EXISTSがTRUEを返した行（この場合はid = 5の行）のみが、最終的なクエリ結果に含まれます

したがって、test5さんがordersテーブルのどのcustomer_idとも一致しないために、このような結果が得られるのです。

この動作は、NOT INの動作とは対照的です。NOT INは、NULLとの比較結果を考慮するため、NULLが存在する場合に予期しない結果を返してしまいます。


# まとめ
今回の記事では、サブクエリで使用されるテーブル列にNULLが含まれる場合のNOT INとNOT EXISTSの動作の違いについてまとめました。

NULLの扱いについては、IS NULLやIS NOT NULLを使用することが一般的に推奨されます。これらの演算子は、NULLを直接比較することができ、NULLに関連する問題を回避する事が出来ます。

また、適切なDB設計を行うことで、このようなケースを未然に防ぐことができるかもしれませんが、実際の開発現場では様々な制約条件が存在することがあるため、NOT INとNOT EXISTSの動作の違いを理解しておこうと思います。