The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement FROM clause Implicit to-many path JOIN

previous : next

Implicit to-many path JOIN

Supported by ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Support for to-many paths (implicit or explicit) has been added in 3.19. While explicit to-many paths are very powerful, users may want the convenience of the implicit to-many paths just like the implicit to-one paths. However, jOOQ doesn't support these out of the box like other ORMs might do, and as users might expect in case of simple examples. Take the following "obvious" example, for instance:

// Get all authors and count their books
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count(AUTHOR.book().ID))
      .from(AUTHOR)
      .groupBy(AUTHOR.ID)
      .fetch();

It reads nicely: "Get all authors and count their books.". The expected query produced by this is:

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(BOOK.ID)
FROM AUTHOR
LEFT JOIN BOOK ON BOOK.AUTHOR_ID = AUTHOR.ID
GROUP BY AUTHOR.ID

Another cool example is this clever ANTI JOIN:

// Get all authors without any book
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .where(AUTHOR.book().ID.isNull())
      .fetch();

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM AUTHOR
LEFT JOIN BOOK
  ON BOOK.AUTHOR_ID = AUTHOR.ID
WHERE BOOK.ID IS NULL

But if the above is possible, then the following counter example would produce very surprising results! One would expect the inverse of an ANTI JOIN to produce a SEMI JOIN, but that wouldn't be what happens:

// Get all authors with books
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .where(AUTHOR.book().ID.isNotNull())
      .fetch();

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM AUTHOR
LEFT JOIN BOOK
  ON BOOK.AUTHOR_ID = AUTHOR.ID
WHERE BOOK.ID IS NOT NULL

Now, we get duplicate authors. One per book they've written, due to the cartesian product created by the LEFT JOIN.

To prevent this, the default behaviour when encountering an implicit to-many join path expression an exception that is thrown.

To prevent this, users have 2 options:

• Override the default with Settings.renderImplicitJoinToManyType. This applies to all queries and removes the above scalar subquery protection for power users who know what they're doing.
• Use explicit path joins to specify that indeed, a LEFT JOIN (or any other type of JOIN) is indeed desired, see example below:

// Get all authors with books
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .from(AUTHOR)
      .leftJoin(AUTHOR.book()) // Now, the LEFT JOIN is explicit and cartesian products aren't accidental.
      .where(AUTHOR.book().ID.isNotNull())
      .fetch();

The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement FROM clause Implicit to-many path JOIN

previous : next