|The jOOQ User Manual : SQL building : Conditional expressions : Quantified comparison predicate||previous : next|
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.
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
SOME. Note that the SQL standard defines
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.
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.
This example using jOOQ:
Translates to the following dialect specific expressions:
-- ASE, AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, DB2, DERBY, FIREBIRD, H2, HANA, HSQLDB, INFORMIX, MARIADB, MYSQL, ORACLE, -- POSTGRES, SQLSERVER, SYBASE, TERADATA, VERTICA, YUGABYTEDB BOOK.AUTHOR_ID = ANY ( SELECT AUTHOR.ID FROM AUTHOR ) -- ACCESS, BIGQUERY, EXASOL, MEMSQL, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE /* UNSUPPORTED */