The IS DISTINCT FROM predicate

Posted on by lukaseder
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

Published by lukaseder

I made jOOQ

5 thoughts on “The IS DISTINCT FROM predicate”

Leave a Reply