はじめに
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 を使用して、
アルバムの購入回数と等しくない購入回数である本のタイトル
を取得する命令を考えます。
SELECT title FROM books
WHERE num_of_purchase NOT IN (
SELECT num_of_purchase FROM albums
);
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スクリプト
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スクリプト
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 は置き換えられないケースがあると言うのはハマるポイントに違いありません。
忘れないでおきたいです。