Quantified LIKE ANY predicates in jOOQ 3.12

Posted on by lukaseder

Quantified comparison predicates

One of SQL’s weirdes features are quantified comparison predicates. I’ve hardly ever seen these in the wild: 

SELECT *
FROM t
WHERE id = ANY (1, 2, 3)
The above example is equivalent to using the much more readable IN predicate: 

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 conveniently than otherwise, such as: 

SELECT *
FROM t
WHERE (a, b) > ALL (
  SELECT x, y
  FROM u
)
This is the same thing as writing the more verbose, and in my opinion, a bit 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 that.

Quantified LIKE predicate

Unfortuantely, the SQL standard and most implementations support the above quantified comparison predicates only for the <, <=, >, >=, =, != comparison operators. Not for other predicate types. For example, the LIKE predicate would profit greatly from such a syntax: 

SELECT *
FROM customers
WHERE last_name LIKE ANY ('A%', 'B%', 'C%')
The syntax is immediately understandable and translates to 

SELECT *
FROM customers
WHERE last_name LIKE 'A%'
OR last_name LIKE 'B%'
OR last_name LIKE 'C%'
… which is much less convenient to write! Furthermore, imagine producing such patterns from a subquery: 

SELECT *
FROM customers
WHERE last_name LIKE ANY (
  SELECT pattern
  FROM patterns
  WHERE pattern.customer_type = customer.customer_type
)
This is a bit trickier 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 this a useful SQL feature to support?

jOOQ 3.12 support for this

jOOQ does support this syntax starting from 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 the previously mentioned emulations are available. You can play around with it downloading jOOQ: https://www.jooq.org/download Or directly on our website: https://www.jooq.org/translate

Published by lukaseder

I made jOOQ

4 thoughts on “Quantified LIKE ANY predicates in jOOQ 3.12”

  1. I would write
    SELECT *
    FROM customers
    WHERE EXISTS (
    SELECT NULL
    FROM patterns
    WHERE patterns.customer_type = customer.customer_type
    AND customer.last_name like patterns.pattern
    )
    in Oracle

    Reply

    1. Hi Matthias,

      Sure, that’s another option for this particular simple case. When more advanced syntax elements appear in the pattern producing subquery (e.g. window functions), it may get a bit more hairy, and a second level of nesting might be needed, which unfortunately isn’t supported in all RDBMS or RDBMS versions…

      Reply

    1. Thanks for your comment, Dieter. Yes, I’ve seen only few SQL dialects that don’t. It’s been in the SQL standard from the very beginning.

      Reply

Leave a Reply