Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
ON KEY clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
All of INNER JOIN, OUTER JOIN, SEMI JOIN, ANTI JOIN require a join predicate.
One way to supply this join predicate is the ON KEY clause, which allows for conveniently joining two tables based on their FOREIGN KEY relationship, assuming the relevant meta data is known to jOOQ via code generation:
SELECT * FROM AUTHOR JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
create.select()
.from(AUTHOR)
.join(BOOK).onKey()
.fetch();
There are different overloads of this onKey() method. The above one is applicable when there are no ambiguous paths between the two joined tables. If there are several FOREIGN KEY declarations (e.g. a book has an AUTHOR_ID and a CO_AUTHOR_ID), then you can pass the org.jooq.ForeignKey reference to the method, instead, to resolve the ambiguity.
SELECT * FROM AUTHOR JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
create.select()
.from(AUTHOR)
.join(BOOK).onKey(Keys.FK_BOOK_AUTHOR)
.fetch();
Feedback
Do you have any feedback about this page? We'd love to hear it!