【SQL】IN句とNULLの怖さ

はじめに

業務で最近SQLをよく読むことが多くなったのだが、その際にNULL関連で勉強になったことがあったので備忘と啓発のための記事です

要約

samplesテーブル

id	name
1	SQL
2	Python
3	JavaScript
NULL	C#

idが2でも3でもNULLでもないレコードを検索したい
今回の場合、ID=1であるレコードだけを出力したい

SELECT * FROM samples WHERE id NOT IN (2, 3, NULL);

↓出力結果(対象レコードなし)

id	name

NULLの比較によって検索条件が想定外のものになってしまうのだろう

• オンライン実行環境での動作確認

原因

検索条件を式変形していくと原因らしいものを特定できた

id NOT IN (2, 3, NULL)
=(IN句をORに同値変換)
NOT ((id = 2) OR (id = 3) OR (id = NULL))
=(∵ドモルガンの法則)
(id <> 2) AND (id <> 3) AND (id <> NULL)
=(∵id <> NULLは常にunknownを返す)
(id <> 2) AND (id <> 3) AND unknown
=(∵true AND unknown = unknown, false AND unknown = false)
false もしくは unknown

よって必ずtrueになることはないので条件に合うレコードは存在しなくなる

暫定対応策

NULLを特別扱いするのが一番簡単

SELECT * FROM samples WHERE id NOT IN (2, 3) AND id IS NOT NULL;

• オンライン実行環境での動作確認

考えられる問題

対策でNULLを特別扱いすればよいとは言ったが、例えば以下の場合に考慮しにくい。

名簿テーブル

id	部署	役割
1	S	A
2	S	B
3	S	C
4	T	A
5	T	D
6	T	NULL

T部署に存在しないS部署の役割を探す場合
今回の場合、S(A, B, C) でT(A, D, NULL)なので役割BとCを出力して欲しい

SELECT 役割 FROM 名簿
WHERE
    役割 NOT IN
        (SELECT 役割 FROM 名簿 WHERE 部署 = 'T')
    AND 部署 = 'S'
;

これで残念ながらIN句にNULLが入ってしまい出力は0レコードとなってしまう

• オンライン実行環境での動作確認

上記のSQLのように「存在する/しない」を判断するならば以下のようにEXISTSを使って書いた方が安全だろう

SELECT 役割 FROM 名簿 m1
WHERE
	NOT EXISTS (
		SELECT 1 FROM 名簿 m2
		WHERE m2.部署 = 'T' AND m2.役割 = m1.役割
	)
    AND s1.部署 = 'S'
;

• オンライン実行環境での動作確認

結論として、SQLを読み書きする際は常にNULLを意識した方が良いのだろう