The jOOQ User Manual : SQL building : Conditional expressions : Quantified comparison predicate | previous : next |
New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6
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.
Feedback
Do you have any feedback about this page? We'd love to hear it!