| The jOOQ User Manual : SQL building : SQL Statements (DML) : The SELECT statement : JOIN operator | previous : next |
New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3
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!