The IS DISTINCT FROM predicate

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

SQL and booleans, some trivia

Some trivia about SQL and booleans:

SQL 1992 defines three values for a boolean:

<truth value> ::=
        TRUE
      | FALSE
      | UNKNOWN

True booleans aren’t always supported, though. Here’s the truth table of boolean support:

SQL Dialect Supports booleans
DB2 0 (use 1/0 instead)
Derby true (you can safely use true/false)
H2 true
HSQLDB true
Ingres true
MySQL true
Oracle 0
Postgres true
SQL Server 0
SQLite 0
Sybase ASE 0
Sybase SQL Anywhere 0

Trivia… but nice to know.