|The jOOQ User Manual : SQL building : Table expressions : Joined tables : PARTITION BY||previous : next|
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Standard SQL (e.g. implemented by Oracle) ships with a special syntax available for OUTER JOIN clauses. This can be used to fill gaps for simplified analytical calculations. jOOQ only supports putting the
PARTITION BY clause to the right of the
OUTER JOIN clause. The following example will create at least one record per AUTHOR and per existing value in BOOK.PUBLISHED_IN, regardless if an AUTHOR has actually published a book in that year.
SELECT * FROM AUTHOR LEFT OUTER JOIN BOOK PARTITION BY (PUBLISHED_IN) ON BOOK.AUTHOR_ID = AUTHOR.ID
create.select() .from(AUTHOR) .leftOuterJoin(BOOK) .partitionBy(BOOK.PUBLISHED_IN) .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)) .fetch();