Quantified comparison predicates
One of the strangest features of SQL are quantified comparison predicates. I’ve hardly ever seen one in the wild:
SELECT * FROM t WHERE id = ANY (1, 2, 3)
The example above is equivalent to using the much more readable method
SELECT * FROM t WHERE id IN (1, 2, 3)
This equivalence is defined in the SQL standard. There are more esoteric cases that could be solved using such quantified comparison predicates more easily than not, such as:
SELECT * FROM t WHERE (a, b) > ALL ( SELECT x, y FROM u )
It’s the same as writing the most verbose, and in my opinion, a little less readable:
SELECT * FROM t WHERE (a, b) > ( SELECT x, y FROM u ORDER BY x, y FETCH FIRST ROW ONLY )
Assuming, of course, that your RDBMS can compare row value expressions like this.
Quantified LIKE predicate
Unfortunately, the SQL standard and most implementations support the above quantified comparison predicates only for the
<, <=, >, >=, =, != comparison operators. Not for other types of predicates. For example, the
LIKE predicate would benefit greatly from such a syntax:
SELECT * FROM customers WHERE last_name LIKE ANY ('A%', 'B%', 'C%')
The syntax is immediately understandable and results in
SELECT * FROM customers WHERE last_name LIKE 'A%' OR last_name LIKE 'B%' OR last_name LIKE 'C%'
… Which is much less practical to write!
Additionally, imagine producing such models from a subquery:
SELECT * FROM customers WHERE last_name LIKE ANY ( SELECT pattern FROM patterns WHERE pattern.customer_type = customer.customer_type )
It’s a little harder to emulate in standard SQL. For example, in PostgreSQL we could write:
SELECT * FROM customers WHERE true = ANY ( SELECT last_name LIKE pattern FROM patterns WHERE pattern.customer_type = customer.customer_type )
In this case, we can use Boolean types. Oracle would make this a bit more difficult:
SELECT * FROM customers WHERE 1 = ANY ( SELECT CASE WHEN last_name LIKE pattern THEN 1 WHEN NOT(last_name LIKE pattern) THEN 0 ELSE NULL END FROM patterns WHERE pattern.customer_type = customer.customer_type )
Wouldn’t that be a useful SQL feature to support?
jOOQ 3.12 support for this
jOOQ supports this syntax as of jOOQ 3.12. You can now write
ctx.selectFrom(CUSTOMERS) .where(CUSTOMERS.LAST_NAME.like(any("A%", "B%", "C%"))) .fetch(); ctx.selectFrom(CUSTOMERS) .where(CUSTOMERS.LAST_NAME.like(any( select(PATTERNS.PATTERN) .from(PATTERNS) .where(PATTERN.CUSTOMER_TYPE.eq(CUSTOMER.CUSTOMER_TYPE)) ))) .fetch();
All of the previously mentioned emulations are available. You can play with it by downloading jOOQ:
Or directly on our website: