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
JOIN operator
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ supports many different types of standard and non-standard SQL JOIN operations. All of these JOIN methods can be called on org.jooq.Table
types the (more info in joined tables section), or directly after the FROM clause for convenience. The following example joins AUTHOR and BOOK
DSLContext create = DSL.using(connection, dialect); // Call "join" directly on the AUTHOR table Result<?> result = create.select() .from(AUTHOR.join(BOOK) .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))) .fetch(); // Call "join" on the type returned by "from" Result<?> result = create.select() .from(AUTHOR) .join(BOOK) .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)) .fetch();
The two syntaxes will produce the same SQL statement. However, calling "join" on org.jooq.Table
objects allows for more powerful, nested JOIN expressions (if you can handle the parentheses):
SELECT * FROM AUTHOR LEFT OUTER JOIN ( BOOK JOIN BOOK_TO_BOOK_STORE ON BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID ) ON BOOK.AUTHOR_ID = AUTHOR.ID
// Nest joins and provide JOIN conditions only at the end create.select() .from(AUTHOR .leftOuterJoin(BOOK .join(BOOK_TO_BOOK_STORE) .on(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID))) .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))) .fetch();
- See the section about conditional expressions to learn more about the many ways to create
org.jooq.Condition
objects in jOOQ. - See the section about table expressions to learn about the various ways of referencing
org.jooq.Table
objects in jOOQ
For more information about the different types of join, please refer to the joined tables section.
Feedback
Do you have any feedback about this page? We'd love to hear it!