This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
UNIQUE predicate is defined by the SQL standard, yet hardly any database implements this feature. It is a standalone predicate (much like the EXISTS predicate) which is used to check for uniqueness of rows returned by a given subquery. An example of an
UNIQUE predicate can be seen here:
UNIQUE (SELECT PUBLISHED_IN FROM BOOK WHERE AUTHOR_ID = 3) NOT UNIQUE (SELECT PUBLISHED_IN FROM BOOK WHERE AUTHOR_ID = 3)
unique(create.select(BOOK.PUBLISHED_IN).from(BOOK) .where(BOOK.AUTHOR_ID.eq(3))); notUnique(create.select(BOOK.PUBLISHED_IN).from(BOOK) .where(BOOK.AUTHOR_ID.eq(3)));
The first example above evaluates to
TRUE only if all books written by the given author were published in distinct years, whereas the second example will be
TRUE if the author published at least two books within the same year.
Currently jOOQ emulates the
UNIQUE predicate for all databases using an
EXISTS predicate with a
GROUP BY subquery wrapping the original subquery:
NOT EXISTS ( SELECT 1 FROM ( SELECT PUBLISHED_IN FROM BOOK WHERE AUTHOR_ID = 3 ) T WHERE (T.PUBLISHED_IN) IS NOT NULL GROUP BY T.PUBLISHED_IN HAVING COUNT(*) > 1 )
Be aware that (as mandated by the SQL standard) any rows returned by the subquery having
NULL values for any of the projected columns will be ignored by the
UNIQUE predicate. Also, for a subquery which doesn't return any rows (or all rows have at least one
NULL value) the
UNIQUE predicate evaluates to
This example using jOOQ:
Translates to the following dialect specific expressions:
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, COCKROACHDB, DB2, DERBY, DUCKDB, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, -- MARIADB, MEMSQL, MYSQL, ORACLE, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA NOT EXISTS ( SELECT 1 FROM ( SELECT BOOK.PUBLISHED_IN FROM BOOK ) t WHERE t.PUBLISHED_IN IS NOT NULL GROUP BY t.PUBLISHED_IN HAVING count(*) > 1 ) -- AURORA_POSTGRES, POSTGRES, REDSHIFT, YUGABYTEDB NOT EXISTS ( SELECT 1 FROM ( SELECT BOOK.PUBLISHED_IN FROM BOOK ) t WHERE (t.PUBLISHED_IN) IS NOT NULL GROUP BY t.PUBLISHED_IN HAVING count(*) > 1 ) -- H2 UNIQUE ( SELECT BOOK.PUBLISHED_IN FROM BOOK )
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Do you have any feedback about this page? We'd love to hear it!