The jOOQ User Manual : SQL building : Table expressions : Joined tables : APPLY or LATERAL

previous : next

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.

The jOOQ User Manual : SQL building : Table expressions : Joined tables : APPLY or LATERAL

previous : next