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
DISTINCT predicate (degree > 1)
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The DISTINCT predicate is also supported for row value expressions of degree higher than 1. If your database does not support row value expression comparison predicates, jOOQ emulates them the way they are defined in the SQL standard:
(FIRST_NAME, LAST_NAME) IS DISTINCT FROM ('John', 'Doe')
(FIRST_NAME, LAST_NAME) IS NOT DISTINCT FROM ('John', 'Doe')
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).isNotDistinctFrom("John", "Doe")
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).isNotDistinctFrom("John", "Doe")
Dialect support
This example using jOOQ:
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).isNotDistinctFrom("John", "Doe")
Translates to the following dialect specific expressions:
ASE, Exasol, Oracle, SQLDataWarehouse, SQLServer, Vertica
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME INTERSECT SELECT 'John', 'Doe' )
Aurora MySQL, MariaDB, MySQL
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) <=> ('John', 'Doe')
Aurora Postgres, BigQuery, CockroachDB, DuckDB, H2, HSQLDB, Postgres, Snowflake, Trino, YugabyteDB
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) is not distinct from ('John', 'Doe')
ClickHouse
arrayUniq(ARRAY(
TUPLE (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME),
TUPLE ('John', 'Doe')
)) = 1
DB2
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM SYSIBM.DUAL INTERSECT SELECT 'John', 'Doe' FROM SYSIBM.DUAL )
Derby
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM SYSIBM.SYSDUMMY1 INTERSECT SELECT 'John', 'Doe' FROM SYSIBM.SYSDUMMY1 )
Firebird
NOT EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM RDB$DATABASE UNION SELECT 'John', 'Doe' FROM RDB$DATABASE OFFSET 1 ROWS )
Hana, Sybase
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM SYS.DUMMY INTERSECT SELECT 'John', 'Doe' FROM SYS.DUMMY )
Informix
EXISTS (
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
INTERSECT
SELECT 'John', 'Doe'
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
)
MemSQL
EXISTS ( SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM DUAL INTERSECT SELECT 'John', 'Doe' FROM DUAL )
Redshift
NOT ((AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) is distinct from ('John', 'Doe'))
SQLite
(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) IS ('John', 'Doe')
Teradata
EXISTS (
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM (
SELECT 1 AS "dual"
) AS "dual"
INTERSECT
SELECT 'John', 'Doe'
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!