Available in versions: Dev (3.16) | Latest (3.15) | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | 3.7

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!

The jOOQ Logo