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

```
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
```

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