A Hidden Benefit of Implicit Joins: Join Elimination

One of jOOQ’s key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises – unless some emulation is required to make a query work, of course. This means that while join elimination is a powerful feature of many RDBMS, it isn’t part of jOOQ’s feature set, so far.

This changes, to some extent, with jOOQ 3.19, and #14992, for implicit path joins only. So far, when you write:

ctx.select(ACTOR, ACTOR.film().category().NAME)
   .from(ACTOR)
   .fetch();

The resulting join tree of this query may look similar to this:

FROM
  actor
    LEFT JOIN film_actor ON actor.actor_id = film_actor.actor_id
    LEFT JOIN film ON film_actor.film_id = film.film_id
    LEFT JOIN film_category ON film.film_id = film_category.film_id
    LEFT JOIN category ON film_category.category_id = category.category_id

But, the FILM table isn’t really needed in this particular query, because no columns from it are being projected, and the presence of primary / foreign keys guarantees equivalence if we just skip the table in the join tree:

FROM
  actor
    LEFT JOIN film_actor ON actor.actor_id = film_actor.actor_id
    LEFT JOIN film_category ON film_actor.film_id = film_category.film_id
    LEFT JOIN category ON film_category.category_id = category.category_id

As soon as any column from the FILM table is projected (or referenced, in general), then the table re-appears in the join tree. E.g. for this query:

ctx.select(ACTOR, ACTOR.film().category().NAME)
   .from(ACTOR)
   // This means we have to add the FILM table again to the join tree:
   .where(ACTOR.film().TITLE.like("A%"))
   .fetch();

In many RDBMS, this doesn’t really matter, because the RDBMS may do the same optimisation, but in some, there’s a big difference. This is a great optimisation in particular because with implicit path joins, jOOQ users can’t really hand-write these optimisations as they’re not authoring the join tree in the FROM clause themselves.

Why implement this only in jOOQ 3.19

Before jOOQ 3.19, there was no support for to-many path joins, and particularly, not for many-to-many path joins, which skip the relationship table. But now, users can write:

// This
ACTOR.film().category().NAME

// Is short (and equivalent) for this:
ACTOR.filmActor().film().filmCategory().category().NAME

Note that the above examples assume that the new Settings.renderImplicitJoinToManyType flag is set to LEFT_JOIN. By default, implicit to-many joins aren’t supported because of their weird semantics in terms of query cardinalities as explained in this blog post. By default, such paths have to be declared explicitly in the FROM clause:

ctx.select(ACTOR, ACTOR.film().category().NAME)
   .from(
       ACTOR,
       ACTOR.film(),
       ACTOR.film().category())
   .fetch();

Or, just:

ctx.select(ACTOR, ACTOR.film().category().NAME)
   .from(
       ACTOR,
       ACTOR.film().category())
   .fetch();