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
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
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