Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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, Databricks, Exasol, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Trino
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!