Available in versions: Dev (3.20) | Latest (3.19)

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 JOIN types, 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!

The jOOQ Logo