postgresql - 比较两个可能为空的字段

Question

如果 a.unitnum 和 b.unitnum 都为 null，是否有比较运算符使 a.unitnum = b.unitnum 为 true？似乎 a.unitnum IS b.unitnum 无效

Answer 1

是的，有 IS DISTINCT FROM and IS NOT DISTINCT FROM

postgres=# \pset null ****
Null display is "****".
postgres=# select null = null;
┌──────────┐
│ ?column? │
╞══════════╡
│ ****     │
└──────────┘
(1 row)

postgres=# select null is not distinct from null;
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

postgres=# select 10 = null;
┌──────────┐
│ ?column? │
╞══════════╡
│ ****     │
└──────────┘
(1 row)

postgres=# select 10 is distinct from null;
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

postgres=# select 10 is not distinct from null;
┌──────────┐
│ ?column? │
╞══════════╡
│ f        │
└──────────┘
(1 row)

postgres=# select 10 is not distinct from 20;
┌──────────┐
│ ?column? │
╞══════════╡
│ f        │
└──────────┘
(1 row)