The LIKE ANY quantified predicates in jOOQ 3.12 – Java, SQL and jOOQ.


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 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 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:
https://www.jooq.org/download

Or directly on our website:
https://www.jooq.org/translate



Source link

Leave a Reply

Your email address will not be published. Required fields are marked *