サブクエリ中のNULL比較

postgres DB 技術

だれにも気づかれないレベルでさりげなくブログのタイトルを変えた。

それはどうでもいいとして、サブクエリにNULL比較を含むときの挙動で混乱したので書く。たぶんみんな知ってること。ぼくは知らなかった。なぜならポンコツだから。

混乱したこと

UsersとAngelsという2つのテーブルがある。

Users

user_name
山田太郎
鈴木健太
ガッキー

Angels

angel_id angel_name
0 アサエル
1 ガブリエル
2 ミカエル
3 ガッキー

ユーザ一覧から、「天使ではないユーザ」を抜き出す以下のSQLを実行する。

select * from Users
where user_name not in (select angel_name from Angels);

この結果は以下になる。

user_name
山田太郎
鈴木健太

3人のユーザのうち、天使の一員であるガッキーを除いた2人が結果に出力される。ここまでは普通。

ここで、名称不明の謎の天使が追加されたとする。

angel_id angel_name
0 アサエル
1 ガブリエル
2 ミカエル
3 ガッキー
4 NULL

これで、さっきと同じSQLを実行してみると、結果は以下になる。

user_name

結果は0人。圧倒的0人。天使であるガッキーが出力されないのは当然として、その他の2人も出力されなくなる。この結果が、直感とは反してるようにみえて混乱する。 上記のSQLは「天使でないユーザ」をみつけるSQLとして妥当なようにみえるのに。

なにがおきているのか

ぼくのポンコツすぎる頭ではサブクエリのままでは理解不能なので、等価な処理に書き換えてみる。

select * from Users
where user_name not in (select angel_name from Angels);

inをanyで置き換えて、

select * from Users
where not(user_name = any(select angel_name from Angels));

anyをorで置き換えて、

select * from Users
where not(
    user_name = 'アサエル'
    or user_name = 'ガブリエル'
    or user_name = 'ミカエル'
    or user_name = 'ガッキー'
    or user_name = NULL
);

ド・モルガンの法則により、

select * from Users
where (
    user_name <> 'アサエル'
    and user_name <> 'ガブリエル'
    and user_name <> 'ミカエル'
    and user_name <> 'ガッキー'
    and user_name <> NULL
);

ここで最後の条件 user_name <> NULL は「UNKNOWN」という扱いになる。そのため、検索条件は常に棄却され、検索結果は0件になる。

なんでこうなるのか

もとのSQLの状態で、なぜ混乱したのか。本質的には、SQLの集合論的な挙動をぼくのポンコツ頭が理解できていなかったことに起因している。どういうことか。

ぼくの頭は非集合論的で、以下のような挙動をイメージしていた。

「山田太郎」は天使か？「鈴木健太」は天使か？「ガッキー」は天使か？をそれぞれ評価し、天使ではないものを出力する。

そのため、AngelsにNULLが含まれる場合でも、検索結果に「山田太郎」と「鈴木健太」は出力されるべきでは、と考えていた。

実際の集合論的な挙動は以下のようになる。

「山田太郎」「鈴木健太」「ガッキー」を要素とする集合Aのうち、天使の集合Bの補集合にあたる集合を出力する。

ここで、集合BにNULLが含まれる場合、集合Aと集合Bとの間でいかなる演算も成立せず、結果は不明(UNKNOWN)となる。

どうすればいいか

NULLとの比較をしないようにすればいい。

-- AngelsからNULL以外を抜き出して比較
select * from Users
where user_name not in (select angel_name from Angels
    where user_name is not NULL);

-- NULLだったら空文字に置き換えて比較
select * from Users
where user_name not in (select
    case when user_name is NULL then ''
    else user_name
from Angels);

そもそも、angel_nameがNOT NULLならこんなことを考えなくていい。謎の天使、禁止。ちゃんと名前を確定させてください。

X-LAWS

天使といえば、週刊少年ジャンプにて1998-2004年連載された漫画『シャーマンキング』に登場するチームX-LAWSがあまりにも有名。スポーツカーを媒体とし、天使の霊をO.S.する。その設定やデザインがめちゃくちゃかっこよくて厨二心を魅了された同年代も多いと思うが、そのキャラデザにおいて機動戦士ガンダムのギャンがモデルになっていることは意外と知られていない。

[まとめ買い] シャーマンキング（ジャンプコミックスDIGITAL）

[まとめ買い] シャーマンキング（ジャンプコミックスDIGITAL）

参考