SQLでNULLとの比較結果をTRUE/FALSEにしたいときはIS DISTINCT FROM演算子を使う

一般的にSQLではNULLとの比較に通常の値比較=ではなくIS NULLを使います。=でnullと値を比較してしまうとUNKNOWNとなる処理系が多いためです。

そこで

• 値とnullの比較はfalseに
• nullとnullの比較はtrueに

という比較がしたい時があるかもしれません。そんな時にはIS DISTINCT FROMもしくはIS NOT DISTINCT FROMという比較演算子が使えます。

A	B	A = B	A IS NOT DISTINCT FROM B
0	0	true	true
0	1	false	false
0	null	unknown	false
null	null	unknown	true

検証環境

postgres=# SELECT version();
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.1 on x86_64-apple-darwin13.4.0, compiled by Apple LLVM version 6.0 (clang-600.0.57) (based on LLVM 3.5svn), 64-bit
(1 row)

$ sqlite3 --version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d

PostgreSQLでの例

--nullと値の比較(結果はunknown)
postgres=# select null = 4;
 ?column? 
----------
 
(1 row)

--値と値の比較(結果はtrue/false)
postgres=# select 4 = 4;
 ?column? 
----------
 t
(1 row)

--is distinct fromを使った比較(結果はtrue/false)
postgres=# select null is distinct from  4;
 ?column? 
----------
 t
(1 row)

-- 同等かどうかを調べるときは is not distinctを使う
postgres=# select null is not distinct from  4;
 ?column? 
----------
 f
(1 row)

-- is distinct fromでは値同士も比較できます
postgres=# select 4 is distinct from  4;
 ?column? 
----------
 f
(1 row)

sqliteではIS DISTINCT FROMの代わりにISを使う

sqliteではIS DISTINCT FROMが使えません。代わりにISで比較を行うことができます

sqlite> select 4 = 4;
1
sqlite> select 4 is null;
0
sqlite> select 4 is 4;
1
sqlite> select 4 is 5;
0
sqlite> select null is null;
1

他

MySQLではIS DISTINCT FROM風の比較に<=>が使えます

備考

2018年現在対応しているベンダーは少ないですが、標準SQLのため今後他のベンダーのRDBMSでも利用できるようになる可能性が大いにあります

参考

• https://modern-sql.com/feature/is-distinct-from
• プログラマのためのSQL 16. 3 IS [NOT] DISTINCT FROM 演算子