Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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.
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.
- 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.
Feedback
Do you have any feedback about this page? We'd love to hear it!