Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
DISTINCT predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some databases support the DISTINCT
predicate, which serves as a convenient, NULL
-safe comparison predicate. With the DISTINCT
predicate, the following truth table can be assumed:
-
[ANY] IS DISTINCT FROM NULL
yieldsTRUE
-
[ANY] IS NOT DISTINCT FROM NULL
yieldsFALSE
-
NULL IS DISTINCT FROM NULL
yieldsFALSE
-
NULL IS NOT DISTINCT FROM NULL
yieldsTRUE
For instance, you can compare two fields for distinctness, ignoring the fact that any of the two could be NULL
, which would lead to funny results. This is supported by jOOQ as such:
TITLE IS DISTINCT FROM SUB_TITLE TITLE IS NOT DISTINCT FROM SUB_TITLE
BOOK.TITLE.isDistinctFrom(BOOK.SUB_TITLE) BOOK.TITLE.isNotDistinctFrom(BOOK.SUB_TITLE)
Dialect support
This example using jOOQ:
AUTHOR.FIRST_NAME.isDistinctFrom(AUTHOR.LAST_NAME)
Translates to the following dialect specific expressions:
ASE, Exasol, SQLDataWarehouse, Vertica
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME x INTERSECT SELECT AUTHOR.LAST_NAME x )
Aurora MySQL, MariaDB, MemSQL, MySQL
(NOT(AUTHOR.FIRST_NAME <=> AUTHOR.LAST_NAME))
Aurora Postgres, BigQuery, CockroachDB, DB2, DuckDB, Firebird, H2, HSQLDB, Postgres, Redshift, SQLServer, Snowflake, Trino, YugabyteDB
AUTHOR.FIRST_NAME IS DISTINCT FROM AUTHOR.LAST_NAME
ClickHouse
arrayUniq(ARRAY(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)) = 2
Derby
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME x FROM SYSIBM.SYSDUMMY1 INTERSECT SELECT AUTHOR.LAST_NAME x FROM SYSIBM.SYSDUMMY1 )
Hana, Sybase
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME x FROM SYS.DUMMY INTERSECT SELECT AUTHOR.LAST_NAME x FROM SYS.DUMMY )
Informix
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME x FROM ( SELECT 1 AS dual FROM systables WHERE (tabid = 1) ) AS dual INTERSECT SELECT AUTHOR.LAST_NAME x FROM ( SELECT 1 AS dual FROM systables WHERE (tabid = 1) ) AS dual )
Oracle
decode(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, 1, 0) = 0
SQLite
(AUTHOR.FIRST_NAME IS NOT AUTHOR.LAST_NAME)
Teradata
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME x FROM ( SELECT 1 AS "dual" ) AS "dual" INTERSECT SELECT AUTHOR.LAST_NAME x FROM ( SELECT 1 AS "dual" ) AS "dual" )
Access
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!