# 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

### 3 responses to “The IS DISTINCT FROM predicate”

### Trackbacks / Pingbacks

- April 15, 2014 -

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

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

Thanks a lot for the hint. I’ll add support for that in the next version of jOOQ:

https://github.com/jOOQ/jOOQ/issues/2729