如果您要比较的列中可以有null / empty值，您是否总是需要在WHERE中使用coalesce？

I know can't compare columns that are empty/null to others so often times I find myself writing a query like:

我知道无法将空/空的列与其他列进行比较,所以我经常会发现自己编写的查询如下:

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

This is the way I've always done it, but I'm curious if there's a better way to handle null/empty values. Also I'm curious as to why NULL <comparison operator> anything is UNKNOWN in the first place.

这是我一直这样做的方式,但我很好奇是否有更好的方法来处理null / empty值。另外,我很好奇为什么NULL <比较运算符> 首先是UNKNOWN。

2 个解决方案

#1

No. The use of COALESCE() generally prevents indexes from being used. The equivalent syntax using boolean logic gets cumbersome.

不可以.COALESCE()的使用通常会阻止使用索引。使用布尔逻辑的等效语法变得很麻烦。

The best solution is to use IS DISTINCT FROM:

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

where field1 is distinct from field2

By using built-in operators, there is a better change that Postgres can optimize the query. IS DISTINCT FROM is ANSI standard syntax which is explained in the documentation.

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

#2

A small note: the result for (COALESCE(t1.val, '') <> COALESCE(t2.val, '')) and (t1.val IS DISTINCT FROM t2.val) is not the same, since '' <> NULL is NULL. Example truth table:

一个小注释:(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
        ;

Resulting truth table:

结果真值表:

 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)

#1