Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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, 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, and through the jOOQ parser, yet they do not have a native representation in any dialect.
-
Implicit JOIN: Implicit JOINs are implicit LEFT JOINs that are derived from to-one relationship path expressions. In order to e.g. access the
COUNTRYcolumns from aCUSTOMERrecord, it is possible to writeCUSTOMER.address().city().country().NAME. jOOQ will produce the necessaryLEFT JOINgraph, which is much more tedious to write. -
MULTISET_AGG function: The
MULTISETaggregate function is not natively supported anywhere, even if a native implementation for the MULTISET value constructor exists. - 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.
- UNIQUE predicate: This SQL standard predicate has not yet been implemented in any SQL dialect (it is being considered for H2). An esoteric, yet occasionally useful predicate that is difficult to emulate manually using the EXISTS predicate.
-
ON CONFLICT .. SET ALL TO EXCLUDED and ON DUPLICATE KEY .. SET ALL TO EXCLUDED: Convenience methods for the most common type of
UPSERTstatement.
Feedback
Do you have any feedback about this page? We'd love to hear it!