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
Implicit path JOIN
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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 b 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, and from jOOQ 3.19 also for to-many 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 (or child table name, respectively), 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.
Default JOIN type
The default type of join that is generated is:
-
INNER JOIN
forto-one
path segments with non-nullable parent -
LEFT JOIN
forto-one
path segments with nullable parent - An exception for implicit
to-many
path segments, which haven't been declared in the FROM clause explicitly, otherwise aLEFT JOIN
(see also implicit to-many path joins for details)
These defaults can be overridden with Settings.renderImplicitJoinTypeor Settings.renderImplicitJoinToManyType, respectively, or by specifying an explicit path join
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 JOIN
ed to the path's "root table". If two paths share a common prefix, that prefix is also shared in the join graph.
Known limitations
- 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
Feedback
Do you have any feedback about this page? We'd love to hear it!