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 yields TRUE
[ANY] IS NOT DISTINCT FROM NULL yields FALSE
NULL IS DISTINCT FROM NULL yields FALSE
NULL IS NOT DISTINCT FROM NULL yields TRUE

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, SQLSERVER, 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, FIREBIRD, H2, HSQLDB, POSTGRES, REDSHIFT, SNOWFLAKE, YUGABYTE
AUTHOR.FIRST_NAME IS DISTINCT FROM AUTHOR.LAST_NAME

-- DERBY
NOT (EXISTS (
  SELECT AUTHOR.FIRST_NAME AS x
  FROM SYSIBM.SYSDUMMY1
  INTERSECT
  SELECT AUTHOR.LAST_NAME AS 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.16, see #10141), or translate your own on our website

The jOOQ User Manual : SQL building : Conditional expressions : DISTINCT predicate

previous : next