The jOOQ User Manual : SQL building : Table expressions : Joined tables : APPLY or LATERAL | previous : next |
Available in versions: Dev (3.18) | Latest (3.17) | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
APPLY or LATERAL
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
T-SQL has long known what the SQL standard calls lateral derived tables, lateral joins using the APPLY
keyword. To every row resulting from the table expression on the left, we apply the table expression on the right. This is extremely useful for table-valued functions, which are also supported by jOOQ. Some examples:
DSL.using(configuration) .select() .from(AUTHOR, lateral(select( count(), min(BOOK.PUBLISHED_IN), max(BOOK.PUBLISHED_IN)) .from(BOOK) .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))) ) .fetch();
The above example shows standard usage of the LATERAL
keyword to connect a derived table to the previous table in the FROM clause. A similar statement can be written in T-SQL:
DSL.using(configuration) .select() .from(AUTHOR) .crossApply( select( count(), min(BOOK.PUBLISHED_IN), max(BOOK.PUBLISHED_IN)) .from(BOOK) .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)) ) .fetch()
While not all forms of LATERAL JOIN
have an equivalent APPLY
syntax, the inverse is true, and jOOQ can thus emulate OUTER APPLY
and CROSS APPLY
using LATERAL JOIN
.
LATERAL JOIN
or CROSS APPLY
are particularly useful together with table valued functions, which are also supported by jOOQ.
Feedback
Do you have any feedback about this page? We'd love to hear it!