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.

ABA = BA IS NOT DISTINCT FROM B
00truetrue
01falsefalse
0nullunknownfalse
nullnullunknowntrue

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

Note

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:

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.

On my Own Behalf

If you like this article, you might also like my book SQL Performance Explained or my training.

Another option is to use set 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:0

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

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.

Warning

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

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

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

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

Footnotes

1

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

2

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.

“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license