The IS DISTINCT FROM predicate

lukaseder

13 years ago

The SQL-1999 standard specifies a useful IS DISTINCT FROM predicate, that behaves slightly different from the regular not equal comparison predicate. Here is its simple definition:

8.13 <distinct predicate>

Format
<distinct predicate> ::=
  <row value expression 3> IS DISTINCT FROM <row value expression 4>
  <row value expression 3> ::= <row value expression>
  <row value expression 4> ::= <row value expression>

Purpose of the DISTINCT predicate

The purpose of this DISTINCT predicate is to alleviate handling NULL / UNKNOWN values in predicates. This is best explained using the following truth tables: Not equal comparison predicate To many SQL beginners, the following truth table might appear a bit awkward to memorise, as it is not really intuitive if you know NULL from other languages.

NULL != NULL yields NULL (not FALSE!)
NULL != [ANY] yields NULL (not TRUE!)
[ANY] != NULL yields NULL (not TRUE!)
[ANY] != [ANY] yields TRUE/FALSE

DISTINCT predicate The DISTINCT predicate treats NULL like most other languages do

NULL IS DISTINCT FROM NULL yields FALSE
NULL IS DISTINCT FROM [ANY] yields TRUE
[ANY] IS DISTINCT FROM NULL yields TRUE
[ANY] IS DISTINCT FROM [ANY] yields TRUE/FALSE
NULL IS NOT DISTINCT FROM NULL yields TRUE
NULL IS NOT DISTINCT FROM [ANY] yields FALSE
[ANY] IS NOT DISTINCT FROM NULL yields FALSE
[ANY] IS NOT DISTINCT FROM [ANY] yields TRUE/FALSE

Support of the DISTINCT predicate in various SQL dialects

Not all SQL dialects natively support this predicate. As a matter of fact, only these few open source ones do:

Firebird
H2
HSQLDB
Postgres

MySQL MySQL doesn’t support the SQL standard DISTINCT predicate, but it knows a special equal-to (<=>) operator that behaves the same way The others The other databases currently supported by jOOQ do not implement the DISTINCT predicate. These include

CUBRID
DB2
Derby
Ingres
Oracle
SQL Server
SQLite
Sybase ASE
Sybase SQL Anywhere

However, the DISTINCT predicate is simulated by jOOQ as follows:


CASE WHEN [this] IS     NULL AND [field] IS     NULL THEN FALSE
     WHEN [this] IS     NULL AND [field] IS NOT NULL THEN TRUE
     WHEN [this] IS NOT NULL AND [field] IS     NULL THEN TRUE
     WHEN [this] =               [field]             THEN FALSE
     ELSE                                                 TRUE
END