New versions: Dev (3.16) | Latest (3.15) | 3.14 | 3.13 | 3.12

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:

-- ACCESS, AURORA_MYSQL, BIGQUERY, COCKROACHDB, DERBY, HSQLDB, MARIADB, MEMSQL, MYSQL, SQLITE
NOT (EXISTS (
  SELECT 1
  FROM (
    SELECT BOOK.PUBLISHED_IN
    FROM BOOK
  ) AS t
  WHERE t.PUBLISHED_IN IS NOT NULL
  GROUP BY t.PUBLISHED_IN
  HAVING count(*) > 1
))

-- ASE, DB2, EXASOL, FIREBIRD, HANA, INFORMIX, ORACLE, SNOWFLAKE, SQLDATAWAREHOUSE, SQLSERVER, SYBASE, TERADATA, 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, YUGABYTE
NOT (EXISTS (
  SELECT 1
  FROM (
    SELECT BOOK.PUBLISHED_IN
    FROM BOOK
  ) AS t
  WHERE (t.PUBLISHED_IN) IS NOT NULL
  GROUP BY t.PUBLISHED_IN
  HAVING count(*) > 1
))

-- H2
UNIQUE (
  SELECT BOOK.PUBLISHED_IN
  FROM BOOK
)

-- REDSHIFT
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
))

(These are currently generated with jOOQ 3.16, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo