如果您正在比较的列中可能存在空值/空值，您是否总是需要在 WHERE 中使用合并？

Question

我知道不能比较空无一人/无效的列与他人经常发现自己写的查询：

WHERE field2 <> '' AND coalesce(field1, '') <> coalesce(field2, '')

这是我一直这样做的方式，但是我很好奇是否有更好的方法来处理空/空值。我也很好奇NULL <comparison operator> anything首先是UNKNOWN。

Answer 1

no。COALESCE()的使用通常阻止使用索引。使用布尔逻辑的等效语法变得笨重。

最好的解决方案是使用IS DISTINCT FROM：

where field1 is distinct from field2

通过使用内置运算符，Postgres可以优化查询的更好的更改。IS DISTINCT FROM是ANSI标准语法，在文档中进行了解释。

Answer 2

一个小注意：(COALESCE(t1.val, '') <> COALESCE(t2.val, ''))和(t1.val IS DISTINCT FROM t2.val)的结果是不是>相同的，因为'' <> NULL为null。示例真相表：

CREATE TABLE three
        ( id serial not null primary key
        , val text
        );
insert into three(val) VALUES(NULL), ('') , ('a') , ('b' );

SELECT COALESCE(t1.val, '<NULL>' ) AS t1val     -- <NULL> for readability
        ,COALESCE(t2.val, '<NULL>' ) AS t2val   -- <NULL> for readability
        , (t1.val <> t2.val) AS is_ne
        , (COALESCE(t1.val, '') <> COALESCE(t2.val, '')) AS coa_is_ne
        , (t1.val IS DISTINCT FROM t2.val) AS is_distinct
FROM three t1, three t2
ORDER BY t1.val,t2.val
        ;

---

结果真实表：

 t1val  | t2val  | is_ne | coa_is_ne | is_distinct 
--------+--------+-------+-----------+-------------
        |        | f     | f         | f
        | a      | t     | t         | t
        | b      | t     | t         | t
        | <NULL> |       | f         | t          # <<-- here
 a      |        | t     | t         | t
 a      | a      | f     | f         | f
 a      | b      | t     | t         | t
 a      | <NULL> |       | t         | t
 b      |        | t     | t         | t
 b      | a      | t     | t         | t
 b      | b      | f     | f         | f
 b      | <NULL> |       | t         | t
 <NULL> |        |       | f         | t          # <<-- and here
 <NULL> | a      |       | t         | t
 <NULL> | b      |       | t         | t
 <NULL> | <NULL> |       | f         | f
(16 rows)