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 | 3.10

Quantified comparison predicate

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

If the right-hand side of a comparison predicate turns out to be a non-scalar table subquery, you can wrap that subquery in a quantifier, such as ALL, ANY, or SOME. Note that the SQL standard defines ANY and SOME to be equivalent. jOOQ settled for the more intuitive ANY and doesn't support SOME. Here are some examples, supported by jOOQ:

TITLE = ANY('Animal Farm', '1982')
PUBLISHED_IN > ALL(1920, 1940)
BOOK.TITLE.eq(any("Animal Farm", "1982"));
BOOK.PUBLISHED_IN.gt(all(1920, 1940));

For the example, the right-hand side of the quantified comparison predicates were filled with argument lists. But it is easy to imagine that the source of values results from a subselect.

ANY and the IN predicate

It is interesting to note that the SQL standard defines the IN predicate in terms of the ANY-quantified predicate. The following two expressions are equivalent:

[ROW VALUE EXPRESSION] IN [IN PREDICATE VALUE]
[ROW VALUE EXPRESSION] = ANY [IN PREDICATE VALUE]

Typically, the IN predicate is more readable than the quantified comparison predicate.

Dialect support

This example using jOOQ:

BOOK.AUTHOR_ID.eq(any(select(AUTHOR.ID).from(AUTHOR)))

Translates to the following dialect specific expressions:

ASE, Aurora MySQL, Aurora Postgres, ClickHouse, CockroachDB, DB2, Derby, DuckDB, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MySQL, Oracle, Postgres, SQLServer, Sybase, Teradata, Vertica, YugabyteDB

BOOK.AUTHOR_ID = ANY (
  SELECT AUTHOR.ID
  FROM AUTHOR
)

Access, BigQuery, Exasol, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Trino

/* UNSUPPORTED */

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

References to this page

Feedback

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

The jOOQ Logo