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

はじめに

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

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

テーブルの準備

今回使用するサンプルデータは下記になります。

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', '[e-mail]'),
(2, 'test2', '[e-mail]'),
(3, 'test3', '[e-mail]'),
(4, 'test4', '[e-mail]'),
(5, 'test5', '[e-mail]');

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);

顧客情報のテーブル

id	name	email
1	test1	[e-mail]
2	test2	[e-mail]
3	test3	[e-mail]
4	test4	[e-mail]
5	test5	[e-mail]

注文情報のテーブル

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を実行してみます。

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が不明であるため、比較結果も不明になるためです。

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

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の評価プロセス

-- サブクエリ実行して顧客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を使用したクエリとその実行結果を見ていきましょう。

-- 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の基本的な構文は以下の通りです。

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の評価プロセス

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

-- 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の動作の違いを理解しておこうと思います。