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
ON KEY clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
TheON KEYclause can quickly produce ambiguities as the implicit key path between two tables in a complex join tree isn't always unique. This can even happen for queries that have worked in the past, but due to newFOREIGN KEYconstraints being added to tables, will stop working. Use this clause with caution!
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();
A similar way to join between tables by using the FOREIGN KEY information is implicit JOIN, which offers path-based navigational expressions from child table to parent table. Unlike the ON KEY syntax, implicit joins will never run into ambiguities.
Feedback
Do you have any feedback about this page? We'd love to hear it!