Explicit path JOIN
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Starting from jOOQ 3.19, it is possible to make path joins (as introduced with implicit path JOIN) explicit in the FROM clause:
// Path joins are created implicitly:
create.select(
BOOK.author().FIRST_NAME,
BOOK.author().LAST_NAME,
BOOK.TITLE,
BOOK.language().CD.as("language"))
.from(BOOK)
.fetch();
// Path joins are created explicitly (e.g. using table lists):
create.select(
BOOK.author().FIRST_NAME,
BOOK.author().LAST_NAME,
BOOK.TITLE,
BOOK.language().CD.as("language"))
.from(BOOK, BOOK.language(), BOOK.author())
.fetch();
// Path joins are created explicitly (e.g. using inner joins, with optional ON clause):
create.select(
BOOK.author().FIRST_NAME,
BOOK.author().LAST_NAME,
BOOK.TITLE,
BOOK.language().CD.as("language"))
.from(BOOK)
.join(BOOK.language())
.join(BOOK.author())
.fetch();
This has a few benefits:
- The exact JOIN type can be specified on a per path basis, including more esoteric
JOINtypes, such as for example SEMI JOIN, ANTI JOIN, or APPLY. - It may improve the clarity of the query.
- It allows for correlating subqueries based on join paths
A special case of the putting path expressions in the FROM clause is the implicit path correlation, where a path establishes a correlation to an outer query, rather than a join to a previous table from the FROM clause.
Feedback
Do you have any feedback about this page? We'd love to hear it!