Since jOOQ 3.11, implicit joins have been supported. An implicit join is a
JOIN (mostly a
LEFT JOIN) that is generated implicitly because of the presence of a path expression. If SQL supported the syntax natively, it would look like this:
SELECT cu.first_name, cu.last_name, cu.address.city.country.country FROM customer AS cu
All that is is convenience for a bunch of explicitly written
LEFT JOIN expressions:
SELECT cu.first_name, cu.last_name, co.country FROM customer AS cu LEFT JOIN address AS a USING (address_id) LEFT JOIN city AS ci USING (city_id) LEFT JOIN country AS co USING (country_id)
ctx.select( CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME, CUSTOMER.address().city().country().COUNTRY_) .from(CUSTOMER) .fetch();
So far, this feature was available in
SELECT statements only, not in
Support for implicit join in DML
Starting with jOOQ 3.17 and #7508, the powerful path expressions are now also available in DML statements, such as
DELETE. For example, let’s update all books whose language is English.
In a hypothetical SQL dialect, this could be written as follows:
UPDATE book SET book.status = 'SOLD OUT' WHERE book.language.cd = 'en'; DELETE book WHERE book.language.cd = 'en';
Or, with jOOQ:
ctx.update(BOOK) .set(BOOK.STATUS, SOLD_OUT) .where(BOOK.language().CD.eq("en")) .execute(); ctx.delete(BOOK) .where(BOOK.language().CD.eq("en")) .execute();
The semantics of statements that use to-one implicit join path expressions seems quite clear. The translation of the above statements to actual SQL may look something like this:
Using correlated subqueries
This emulation is straightforward.
It could also be used for implicit
JOIN emulation in
SELECT queries, although the
LEFT JOIN approach is more optimal, as more RDBMS can optimise joins as opposed to correlated subqueries (despite them being equivalent), and we can re-use existing
JOIN trees in case multiple columns are projected from a shared path.
In the current example, there’s only a single column implicitly joined, so the above isn’t too important.
UPDATE book SET status = 'SOLD OUT' WHERE ( SELECT language.cd FROM language WHERE book.language_id = language.id ) = 'en'; DELETE FROM book WHERE ( SELECT language.cd FROM language WHERE book.language_id = language.id ) = 'en';
This approach works in every RDBMS and also recursively, for multiple path segments.
Using DML JOIN
Some RDBMS support some sort of
JOIN syntax also in DML statements and jOOQ can leverage this. Currently, this is being done only for MariaDB, MySQL, MemSQL, and only for
UPDATE (book JOIN language AS l ON book.language_id = l.id) SET book.status = 'SOLD OUT' WHERE l.cd = 'en';
This is pretty much the same thing as what we’ve done already for
SELECT statement. Quite neat that this works out of the box. In fact, it already worked before jOOQ 3.17, we just didn’t officially support it.
Note that other RDBMS support multi-table DML statements, including e.g. PostgreSQL, whose
UPDATE statement has a
FROM clause, or whose
DELETE statement has
USING clause. Unfortunately, this
FROM clause allows only for
INNER JOIN semantics, so there are a few edge cases that cannot be implemented with this syntax yet.
Using updatable views
A few RDBMS support standard SQL updatable views, including inline views that can be updated. Oracle is one of them. In Oracle, while the above
UPDATE .. JOIN syntax from MySQL isn’t supported, something much more powerful can be done:
UPDATE ( SELECT b.*, l.cd FROM book b LEFT JOIN language l ON b.language_id = l.id ) b SET b.status = 'SOLD OUT' WHERE b.cd = 'en'
While you can already use this syntax with jOOQ, manually, jOOQ doesn’t yet translate your implicit
JOIN path expressions to the above, but we soon will, see #13917.