SQL指南書 比較述語とNULL③ NOT IN と NOT EXISTS は同値ではない

SQL 指南書集 目次 - ray88’s diary
■NOT IN を NOT EXISTS で書き換える場合の注意
　パフォーマンスチューニングにおいてINをEXISTSで書き換えることはよく行われる。
　これは問題のない同値変換だが、問題はNOT INをNOT EXISTSで書き換える場合、
　必ずしも結果は一致しない。
【具体例】
Bクラスの東京在住の生徒と年齢が一致しないＡクラスの生徒を選択するクエリを考える

【考察】
以下のようなSQL文の場合、結果は１行も選択されない（山田君のNULLが影響しているため）
NOT IN のサブクエリで使用されるテーブルの選択列にNULLが存在する場合、
ＳＱＬ全体の結果は常に空になる。

/* B クラスの東京在住の生徒と年齢が一致しないA クラスの生徒を選択するSQL？ (p.55) */
SELECT *
FROM class_a
WHERE age NOT IN(SELECT age
				 FROM class_b
				 WHERE city = '東京');

実行結果

■正しいSQL
EXISTS述語は絶対に unknown を返さず、true と false しか返さない。
そのため、IN と EXISTS は同値変換が可能だが、NOT IN とNOT EXISTSは同値ではない。

--正しいSQL：ラリーとボギーが選択される
SELECT *
FROM class_a AS	a
WHERE NOT EXISTS(SELECT *
				 FROM class_b AS b
				 WHERE a.age = b.age
				   AND b.city = '東京');

出力結果

■テーブル作成用SQL

/* 実践編：３. NOT IN とNOT EXISTS は同値ではない */
CREATE TABLE Class_A
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER,
 city VARCHAR(16) NOT NULL );

CREATE TABLE Class_B
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER,
 city VARCHAR(16) NOT NULL );

INSERT INTO Class_A VALUES('ブラウン', 22, '東京');
INSERT INTO Class_A VALUES('ラリー',   19, '埼玉');
INSERT INTO Class_A VALUES('ボギー',   21, '千葉');

INSERT INTO Class_B VALUES('斎藤',  22,   '東京');
INSERT INTO Class_B VALUES('田尻',  23,   '東京');
INSERT INTO Class_B VALUES('山田',  NULL, '東京');
INSERT INTO Class_B VALUES('和泉',  18,   '千葉');
INSERT INTO Class_B VALUES('武田',  20,   '千葉');
INSERT INTO Class_B VALUES('石川',  19,   '神奈川');