【Postgresql】Date型の値ありとNULLを比較した場合の評価

はじめに

テーブル定義でNOT NULLのところにNULLが入ってきたらまずいなと思い、Date型で値ありとNULLを比較したときにどう評価されるのかを調べた。

検証バージョン：PostgreSQL 10.4

テスト用テーブル

-- nullを'NULL'と表示
\pset null NULL

SELECT * FROM table_a;
 id |  foodate   
----+------------
 1  | 2000-01-01
 2  | 2000-01-02
 3  | 2000-01-03
(3 rows)

SELECT * FROM table_b;
 id |  foodate   
----+------------
 1  | 2000-01-01
 2  | 2000-02-01
 3  | NULL
(3 rows)

• id='1'は両者同じ値。
• id='2'は'2000-01-02'と'2000-02-01'なので異なる値。
• id='3'は'2000-01-03'とNULL。 ← これがどう評価されるか。

検証

SELECT 
    a.id,
    a.foodate <> b.foodate as result
FROM 
    table_a a
LEFT JOIN
    table_b b
ON a.id = b.id

 id | result 
----+--------
 1  | f
 2  | t
 3  | NULL

id='3'はFALSEを想定していたが、返ってきたのはNULL。

そして、WHERE句ではNULLがどう評価されるのか。

SELECT 
    a.id
FROM 
    table_a a
LEFT JOIN
    table_b b
ON a.id = b.id
WHERE
    a.foodate = b.foodate;

 id 
----
 1
(1 row)

これは想定内。

SELECT 
    a.id
FROM 
    table_a a
LEFT JOIN
    table_b b
ON a.id = b.id
WHERE
    a.foodate <> b.foodate; --ここだけ変更

 id 
----
 2
(1 row)

'NULLと等しい値はない=>FALSEと評価されid='3'は戻ってくると考えていたのだが返ってこなかった。
条件が一致したものだけが返ってくるようだ。NULLは評価の対象にならずに無視されるということかもしれない。

感想

NULL が無視されるのであれば、当初の気がかりはとりあえず解消だが、PostgreSQL以外のSQLやPostgreSQLでもバージョンが異なれば違う結果となることは考えられるので、この結果を前提にはしないほうが良いでしょう。

追記

その後、SQLの本を読んでいたら本記事の内容は3値論理というしくみによるものらしい。理論を理解するのは難しいですが、ポイントは以下の３つでしょうか。

• NULL の入った値を比較するとunknownが返る。
• 比較式の評価で返ってくるのは true のみ。
• Postgresは unknown を NULL で代替する（a.foodate <> b.foodate as result が NULL だったのは、このためだろう）。

参考:
NULL を比較すると UNKNOWN が返る
3値論理とNULL
第8回　SQLにおける論理演算～なぜ真理を隠すのか～　（1）各DBの真理値型のサポート