|The jOOQ User Manual. Multiple Pages : SQL building : Conditional expressions : Quantified comparison predicate||previous : next|
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.equalAny("Animal Farm", "1982"); BOOK.PUBLISHED_IN.greaterThanAll(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.