はじめに

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

テーブル・データ作成スクリプト

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

`NOT IN`、`NOT EXISTS` を使用した命令

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

NOT IN

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

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 がどのような処理をしているのかを追っていきます。

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がどのような処理をしているのかを追っていきます。

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 は置き換えられないケースがあると言うのはハマるポイントに違いありません。
忘れないでおきたいです。