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.Conditionobjects in jOOQ. - See the section about table expressions to learn about the various ways of referencing
org.jooq.Tableobjects in jOOQ
For more information about the different types of join, please refer to the joined tables section.
References to this page
- Object qualification for columns
- The SELECT statement
- Implicit path JOIN
- WHERE clause
- Generated Tables
- Joined tables
- COUNT
- Nested records
- Conditional expressions
- Operator precedence
- Synthetic SQL clauses
- Optional conditional tables
- STRAIGHT_JOIN
- ANSI JOIN to table lists
- Unnecessary INNER JOIN
- Record vs. TableRecord
- SQL: N+1
Feedback
Do you have any feedback about this page? We'd love to hear it!