Synthetic SQL clauses
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Most of the previously mentioned SQL clauses have a native representation in at least one of jOOQ's supported SQL dialects. For example, when a function like
LPAD() is unavailable, jOOQ produces an equivalent expression for it:
-- MySQL (native support) lpad('a', 10, ' ') -- SQL Server (emulation) (replicate(' ', (10 - len('a'))) + 'a')
// In Java lpad("a", 10, " ")
However, since a lot of SQL is emulated for dialect compatibility, nothing prevents jOOQ from supporting synthetic SQL clauses that do not have any native representation anywhere. An example for this is the quantified like predicate, introduced in jOOQ 3.12 (yes, you should upgrade!), which would be really useful in any database:
(TITLE LIKE '%abc%' OR TITLE LIKE '%def%') (TITLE NOT LIKE '%abc%' OR TITLE NOT LIKE '%def%') (TITLE LIKE '%abc%' AND TITLE LIKE '%def%') (TITLE NOT LIKE '%abc%' AND TITLE NOT LIKE '%def%')
BOOK.TITLE.like(any("%abc%", "%def%")) BOOK.TITLE.notLike(any("%abc%", "%def%")) BOOK.TITLE.like(all("%abc%", "%def%")) BOOK.TITLE.notLike(all("%abc%", "%def%"))
In this section, we briefly list most such synthetic SQL clauses, which are available both through the jOOQ API, yet they do not have a native representation in any dialect.
- Relational Division: Relational algebra supports a divison operator, which is the inverse operator of the cross product.
- SEEK clause: The SEEK clause is a synthetic clause of the SELECT statement, which provides an alternative way of paginating other than the OFFSET clause. From a performance perspective, it is generally the preferred way to paginate.
- SEMI JOIN and ANTI JOIN: Relational algebra defines SEMI JOIN and ANTI JOIN operators, which do not have a representation in any SQL dialect supported by jOOQ (Apache Impala has it, though). In SQL, the EXISTS predicate or IN predicate is used instead.
- Sort indirection: When sorting, sometimes, we want to sort by a derived value, not the actual value of a column. Sort indirection makes this very easy with jOOQ.