Is distinct from

Pg supports two comparison statements IS DISTINCT FROM and IS NOT DISTINCT FROM, these essentially treat NULL as if it was a known value, rather than a special case for unknown.

Mnemonic: DISTINCT means different which is what '<>' tests for

Syntax

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

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

Truth Table

\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

Writing with CASE statements

Both of these sql comparison macros can be written with a CASE statement:

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

Determining different data in tables

Lets say you have two tables, t1, and t2, and they both have columns "foo","bar","baz" and you want to see all rows that are in t1, that aren't in t2. Normally, if you wanted the effect of '=' you could make this really simple:

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

USING (foo,bar,baz) is just a macro for: t1.foo = t2.foo AND t1.bar = t2.bar AND t1.baz = t2.baz

However, if you wanted the treat-null-as-a-known-value alternative you would need: IS NOT DISTINCT FROM. The task because slightly more verbose and daunting when joining a table, because there are no shorthands for the IS NOT DISTINCT FROM form.

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 )