SQLで is null or 〜 を一発でやる is distinct from の話

SQL

環境

  • PostgreSQL 15
    • 本記事の機能はPostgreSQLで試していますが、is distinct from はANSI標準なので、他のDBMSでもサポートされていれば使えます

SQLで null を含むカラムへの条件指定

ご存知のとおり、SQLでの値の比較の際に = とか <> とかで null と比較すると、結果は常に null になります。 このため、null を含むカラムに対して「ある値に一致しない行を、null も含めて出したい」みたいなことをやる場合は is null の条件も加えて比較する必要があります。

例： テーブル nantoka_data

-- 元データ
select * from nantoka_data;
id name num
1 'foo' 100
2 'bar' 200
3 null null 
-- num が null の行は num <> 100 だと抽出されない
select * from nantoka_data where num <> 100;
id name num
2 'bar' 200 
-- is null の条件を加えて、カラム num の値が 100 と一致しない行を全て抽出する
select * from nantoka_data where num is null or num <> 100;
id name num
2 'bar' 200
3 null null

is distinct from を使ってみる

で、これを一発で行う is distinct from というものがあります。

入力のどちらかがnullの場合、通常の比較演算子は真や偽ではなく（「不明」を意味する）nullを生成します。 例えば7 = nullはnullになります。7 <> nullも同様です。 この動作が適切でない場合は、IS [ NOT ] DISTINCT FROM述語を使用してください。

a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

9.2. 比較関数および演算子

これを使って前述のSQLを書き換えると、こんな感じ。

-- select * from nantoka_data where num is null or num <> 100; と同じ
select * from nantoka_data where num is distinct from 100;
id name num
2 'bar' 200
3 null null

ちなみに構文は expression IS DISTINCT FROM expression なので、値だけでなく式が書けます。 このため、値の一致のみでなく、大小比較や like での部分一致なんかもできます。

-- num < 150 に該当しない行を、nullも含めて抽出
select * from nantoka_data where num < 150 is distinct from true;
id name num
2 'bar' 200
3 null null 
-- name like 'b%' に該当しない行を、nullも含めて抽出
select * from nantoka_data where name like 'b%' is distinct from true;
id name num
1 'foo' 100
3 null null

正直ちょっと冗長なので is null or 〜 と書いてもあまり変わらない気がするのと、「どこにも not って書いてないのに否定条件」というところに若干のわかりづらさはありますが、覚えておくとどこかで使えるかもしれません。

参考