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


Although there are standard alternatives to is not distinct from, using a proprietary alternative is often the better choice.

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 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.

On my Own Behalf


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

        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.


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:

        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


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


ANSI_NULLS OFF is deprecated: it’s use may cause errors in future versions of SQL Server.

Also note that it does not affect all equals signs, but only those where one side of the comparison is a variable or the null literal. It does not affect general <expression> = <expression> comparisons.

About the Author

Photo of Markus Winand


Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

Learn more »



Deterministic means that it returns the same output when provided the same input. Current_timestamp is not deterministic, for example.

Side-effects are state changes that remain after the function call (such as modifying SQL data). Calling such functions twice might trigger the side-effects twice, which is not the case for the is not distinct from predicate. Whether or not this is a problem in context of emulating is not distinct from depends on the type of side-effect.


Documentation for Db2/zOS, Db2 LUW, Oracle, and H2.


The operator <=> is often refereed to as spaceship operator. However, the spaceship operator offered by other languages (Perl, PHP, Ruby) serves a different purpose. See “Three-way comparison” on Wikipedia.
