与...不同

Pg 支持两种比较语句 **IS DISTINCT FROM** 和 **IS NOT DISTINCT FROM**，它们本质上将 NULL 视为一个已知的值，而不是一个表示 _未知_ 的特殊情况。

助记符： _DISTINCT 意为不同，这就是 '<>' 测试的内容_

语法

SELECT foo,bar FROM table WHERE foo IS DISTINCT FROM bar;

SELECT foo,bar FROM table WHERE foo IS NOT DISTINCT FROM bar;

真值表

\pset null '<<NULL>>'

select a.a, b.b, a.a IS DISTINCT FROM b.b AS "Is Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a), (VALUES (1), (2), (NULL)) AS b (b);

    a     |    b     | Is Distinct From 
----------+----------+------------------
        1 |        1 | f
        1 |        2 | t
        1 | <<NULL>> | t
        2 |        1 | t
        2 |        2 | f
        2 | <<NULL>> | t
 <<NULL>> |        1 | t
 <<NULL>> |        2 | t
 <<NULL>> | <<NULL>> | f

select a.a, b.b, a.a IS NOT DISTINCT FROM b.b AS "Is Not Distinct From" FROM (VALUES (1), (2), (NULL)) AS a (a), (VALUES (1), (2), (NULL)) AS b (b);

    a     |    b     | Is Not Distinct From 
----------+----------+----------------------
        1 |        1 | t
        1 |        2 | f
        1 | <<NULL>> | f
        2 |        1 | f
        2 |        2 | t
        2 | <<NULL>> | f
 <<NULL>> |        1 | f
 <<NULL>> |        2 | f
 <<NULL>> | <<NULL>> | t

使用 CASE 语句编写

这两个 SQL 比较宏都可以使用CASE语句

foo IS NOT DISTINCT FROM bar

CASE
  -- Act like a normal '=' on non-null values.
  WHEN foo IS NOT NULL AND bar IS NOT NULL
    THEN foo = bar
  -- foo = 'notnull' , bar = 'null' ; then foo <> bar so false
  WHEN foo IS NOT NULL AND bar IS NULL
    THEN false
  -- foo = 'null' , bar = 'null' ; then foo = bar so true
  WHEN foo IS NULL AND bar IS NULL
    THEN true
  ELSE false
END

foo IS DISTINCT FROM bar

CASE
  -- Act like a normal '<>' on non-null values.
  WHEN foo IS NOT NULL AND bar IS NOT NULL
    THEN foo <> bar
  -- foo = 'notnull' , bar = 'null' ; then foo <> bar so true
  WHEN foo IS NOT NULL AND bar IS NULL
    THEN true
  -- foo = 'null' , bar = 'null' ; then foo = bar so false
  WHEN foo IS NULL AND bar IS NULL
    THEN false
  ELSE true
END

确定表中的不同数据

假设你有两个表，t1和t2，它们都有以下列"foo","bar","baz"并且你想查看 t1 中所有不在 t2 中的行。通常，如果你想要 '=' 的效果，你可以非常简单地做到这一点

SELECT foo,bar,baz
FROM t1
LEFT OUTER JOIN t2 USING (foo,bar,baz)
WHERE t1.foo IS NULL

USING (foo,bar,baz) 仅仅是以下内容的宏：t1.foo = t2.foo AND t1.bar = t2.bar AND t1.baz = t2.baz

但是，如果你想要将 null 视为已知值的替代方案，你将需要IS NOT DISTINCT FROM。在连接表时，任务会变得稍微冗长和令人生畏，因为没有IS NOT DISTINCT FROM形式的简写。

SELECT t1.foo,t1.bar,t1.baz
FROM t1
LEFT OUTER JOIN t2 ON (
 t1.foo IS NOT DISTINCT FROM t2.foo
 AND t1.bar IS NOT DISTINCT FROM t2.bar
 AND t1.baz IS NOT DISTINCT FROM t2.baz
)
WHERE ( t2.foo IS NULL )