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

Tags: , , , , ,

3 responses to “The IS DISTINCT FROM predicate”

  1. Chris Bandy says :

    SQLite supports this functionality with the IS and IS NOT operators.

    http://www.sqlite.org/lang_expr.html#binaryops

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,190 other followers

%d bloggers like this: