All versions: 3.11 | Development versions: 3.12

In SQL, a lot of explicit JOIN clauses are written simply to retrieve a parent table's column from a given child table. For example, we'll write:

-- Get all books, their authors, and their respective language
SELECT 
  a.first_name, 
  a.last_name, 
  b.title, 
  l.cd AS language
FROM book b
JOIN author a ON b.author_id = a.id
JOIN language l ON b.language_id = l.id;

-- Count the number of books by author and language
SELECT 
  a.first_name, 
  a.last_name, 
  l.cd AS language, 
  COUNT(*)
FROM book
JOIN author a ON b.author_id = a.id
JOIN language l ON b.language_id = l.id
GROUP BY a.id, a.first_name, a.last_name, l.cd
ORDER BY a.first_name, a.last_name, l.cd

There is quite a bit of syntactic ceremony (or we could even call it "noise") to get a relatively simple job done. A much simpler notation would be using implicit joins:

-- Get all books, their authors, and their respective language
SELECT 
  b.author.first_name, 
  b.author.last_name, 
  b.title, 
  b.language.cd AS language
FROM book b;

-- Count the number of books by author and language
SELECT 
  b.author.first_name, 
  b.author.last_name, 
  b.language.cd AS language, 
  COUNT(*)
FROM book
GROUP BY 
  b.author_id, 
  b.author.first_name, 
  b.author.last_name, 
  b.language.cd
ORDER BY 
  b.author.first_name, 
  b.author.last_name, 
  b.language.cd

Notice how this alternative notation (depending on your taste) may look more tidy and straightforward, as the semantics of accessing a table's parent table (or an entity's parent entity) is straightforward.

From jOOQ 3.11 onwards, this syntax is supported for to-one relationship navigation. The code generator produces relevant navigation methods on generated tables, which can be used in a type safe way. The navigation method names are:

  • The parent table name, if there is only one foreign key between child table and parent table
  • The foreign key name, if there are more than one foreign keys between child table and parent table

This default behaviour can be overridden by using a Code Generator Strategy.

The jOOQ version of the previous queries looks like this:

// Get all books, their authors, and their respective language
create.select(
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.TITLE,
          BOOK.language().CD.as("language"))
      .from(BOOK)
      .fetch();
             
// Count the number of books by author and language
create.select(
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.language().CD.as("language"),
          count())
      .from(BOOK)
      .groupBy(
          BOOK.AUTHOR_ID,
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.language().CD)
      .orderBy(
          BOOK.author().FIRST_NAME,
          BOOK.author().LAST_NAME,
          BOOK.language().CD)
      .fetch();

The generated SQL is almost identical to the original one - there is no performance penalty to this syntax.

How it works

During the SQL generation phase, implicit join paths are replaced by generated aliases for the path's last table. The paths are translated to a join graph, which is always LEFT JOINed to the path's "root table". If two paths share a common prefix, that prefix is also shared in the join graph.

Future versions of jOOQ may choose to generate correlated subqueries or inner joins where this may seem more appropriate, if the query semantics doesn't change through that.

Known limitations

  • Implicit JOINs are currently only supported in SELECT statements (including any type of subquery), but not in the WHERE clause of UPDATE statements or DELETE statements, for instance.
  • Implicit JOINs can currently only be used to access columns, not to produce joins. I.e. it is not possible to write things like FROM book IMPLICIT JOIN book.author
  • Implicit JOINs are added to the SQL string after the entire SQL statement is available, for performance reasons. This means, that VisitListener SPI implementations cannot observe implicitly joined tables
The jOOQ Logo