Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

Implicit join type

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ's very useful implicit JOIN feature can be used to use a path notation to join tables on their actual, or synthetic foreign keys. For example:

// Get all books, their authors, and their respective language

By default, this produces:

  • An INNER_JOIN if all columns of the foreign key are NOT NULL
  • A LEFT_JOIN if the foreign key is nullable / optional
  • A SCALAR_SUBQUERY if the implicit join path is in a DML statement
  • An exception if the path follows a to-many relationship

The above defaults are important to prevent implicit joins from filtering results when placed in clauses that are not meant to filter, such as the SELECT clause or the ORDER BY clause, as well as to prevent them from generating rows in such cases (in the case of to-many joins).

Users may prefer to enforce a different behaviour, including:

  • Always produce a LEFT_JOIN, e.g. because this was the behaviour before jOOQ 3.14
  • Always produce an INNER_JOIN, e.g. because they're migrating off Hibernate / JPA, and depend on Hibernate's implicit joins producing inner joins
  • Always produce a SCALAR_SUBQUERY, to keep scoping of a path local (producing duplicates for shared path segments). This is also what's being done when an implicit join path is rendered in a DML statement.
  • Always THROW an exception.

This change of behaviour can be achieved with the following setting:

Example configuration

Settings settings = new Settings()


Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo