Implicit Path Joins May Now Skip Unnecessary Tables in the Join Tree

One of Jooq’s Key Features So far has Always been to render pretty much exactly the sql that users experts, without any surprises – unhelss 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 jouq’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 projection, and the presence of primary / foreign keys vehicle 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 projection (or referenceed, in general), then the table re -Pears in the join tree. Eg 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 optimization, but in some, there’s a big difference. This is a great optimization in particular decision with implicit path joins, jooq users can’t really hand-wWRITE these optimisations as they’re not authoring the join trees 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_JOINBy default, implicit to-many Joins aren’t supported trust of his 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();