NULL-Aware Comparison: is [not] distinct from

In SQL null is not equal (=) to anything—not even to another null. According to the three-valued logic of SQL, the result of null = null is not true but unknown. SQL has the is [not] null predicate to test if a particular value is null.

With is [not] distinct from SQL also provides a comparison operator that treats two null values as the same.

<expression> IS NOT DISTINCT FROM <expression>

Note that you have to use the negated form with not to arrive at similar logic to the equals (=) operator.

The following truth table highlights the differences between the equals sign (=) and is not distinct from.

The result with equals (=) is unknown if one operator is null. The is not distinct from comparison is true if both values are null or false if only one is null.

Conforming Alternatives

Due to SQL’s three-valued logic, a fully equivalent substitute for A is not distinct from B that works in all SQL databases is surprisingly complex—even when we limit the requirement to cases where evaluating the expressions A and B is deterministic and has no side-effects.0

CASE WHEN (a = b) or (a IS NULL AND b IS NULL)
     THEN 0
     ELSE 1
 END = 0

The result of the expression in the when clause is true if both arguments are equal or both are null. If only one argument is null the result is unknown, not false. This is often not a problem because SQL generally treats unknown like false when making binary decisions such as accepting or rejecting a row for a where clause.

To get the fully equivalent functionality of is not distinct from—i.e. either true or false but never unknown—the case expression reduces the three-valued result into a two-valued one. In some databases is not false can be used instead of the case expression. This technique is explained in “Binary Decisions Based on Three-Valued Results”.

Another option is to use table operators, which use distinct comparisons internally. The following snippet uses intersect to determine a common subset. The two compared sets are just one value each (one row with one column). If it is twice the same value the common subset will be that value. Otherwise the common subset is empty. This logic can be easily tested in the where clause with an exists predicate:1

EXISTS (VALUES (A)
        INTERSECT
        VALUES (B)
       )

This has the advantage that it does not repeat any expressions. Unfortunately, it doesn’t work on all databases due to the use of the values clause. A select from a one-row dummy table can be used to get a conforming and widely supported solution.

Compatibility

The is [not] distinct from predicate was introduced in two steps: SQL:1999 added T151, “DISTINCT predicate”. The optional negation with not was added by SQL:2003 as feature T152, “DISTINCT predicate with negation”.

Proprietary Alternatives

Most database that do not offer is not distinct from offer a proprietary alternative that is more convenient than the conforming alternative described above. The following proprietary features are fully compatible—i.e. they have a two-valued result and never return unknown.

Exists, select without from, intersect

The standard solution using exists, values, and intersect can easily be modified to work on more databases by using select without from instead of the values clause:

EXISTS (SELECT c1
        INTERSECT
        SELECT c2
       )

decode — Db2, Oracle, H2

Db2, Oracle database, and H2 have the proprietary function decode that happens to use is not distinct from semantics internally.2 The following example has the same effect as A is not distinct from B:

DECODE(A, B, 0, 1) = 0

is — SQLite, H2

The is operator of SQLite (documentation) and H2 (documentation) is able to compare two expressions (not just is [not] null), and it has the same semantics as is not distinct from.

<=> — MySQL, MariaDB

MySQL offers the proprietary <=> comparison operator that works like is not distinct from.3

ANSI_NULLS — SQL Server

SQL Server’s deprecated ANSI_NULLS setting makes some equals comparison (=) act as though it was a is not distinct from comparison.