jOOQ 3.17 Supports Implicit Join also in DML – Java, SQL and jOOQ.

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)

With jOOQ, the feature can be used if you’re using code generation:

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 UPDATE, or DELETE.

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 UPDATE or 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 statements:

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.