Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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 NULLyieldsTRUE -
[ANY] IS NOT DISTINCT FROM NULLyieldsFALSE -
NULL IS DISTINCT FROM NULLyieldsFALSE -
NULL IS NOT DISTINCT FROM NULLyieldsTRUE
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, SNOWFLAKE, SQLSERVER,
-- TRINO, YUGABYTEDB
AUTHOR.FIRST_NAME IS DISTINCT FROM AUTHOR.LAST_NAME
-- 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 */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!