UNIQUE predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The 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
)
NULL values
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 TRUE.
Dialect support
This example using jOOQ:
unique(select(BOOK.PUBLISHED_IN).from(BOOK))
Translates to the following dialect specific expressions:
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, CockroachDB, DB2, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, SQLDataWarehouse, SQLServer, SQLite, Snowflake, 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 )
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!