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.

LATERAL

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

LATERAL is a SQL standard table operator to wrap derived tables (or other table expressions, in some dialects), such that the tables and columns declared before the LATERAL derived table become in scope. See APPLY for an alternative, SQL Server specific syntax.

An example:

SELECT *
FROM 
  AUTHOR,
  
  -- All previous objects (i.e. AUTHOR) 
  -- are now in scope for the following subquery
  LATERAL (
    SELECT count(*)
    FROM BOOK
    WHERE BOOK.AUTHOR_ID = AUTHOR.ID -- AUTHOR is in scope
  );
DSL.using(configuration)
   .select()
   .from(
      AUTHOR,
      lateral(
        select(count() 
        .from(BOOK)
        .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
      )
   )
   .fetch();

This is most useful for:

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo