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