Simplifying ANTI JOIN with jOOQ Syntax

ANTI JOIN is a very useful operator from relational algebra. Regrettably, only few dialects support it in terms of SQL syntax, as we’ve written earlier. In jOOQ, you can write it as follows:

ctx.select(AUTHOR.ID)
   .from(AUTHOR)
   .leftAntiJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))

If your RDBMS supports this natively (e.g. ClickHouse, Databricks), then it is rendered as such. Otherwise, jOOQ will translate this to:

SELECT author.id
FROM author
WHERE NOT EXISTS (
  SELECT 1
  FROM book
  WHERE book.author_id = author.id
)

But even with the convenience syntax, it’s a lot to type. Since jOOQ 3.19, to-many join paths are available and can be used here, instead:

ctx.select(AUTHOR.ID)
   .from(AUTHOR)
   .leftAntiJoin(AUTHOR.book())

This does exactly the same thing as the original statement. But what if you prefer working with NOT EXISTS (or EXISTS for SEMI JOIN semantics)? After all, the terms ANTI JOIN and SEMI JOIN may not be what people think of every day, and as such, might not be too readable.

You can still use join paths like shown before! Just write this, instead:

ctx.select(AUTHOR.ID)
   .from(AUTHOR)
   .where(notExists(AUTHOR.book()))

Doesn’t this read very naturally?

“Get all authors for which there does not exist a book”

I.e. all authors that haven’t written any books yet. This is a combination of convenience where:

// This syntax ...
notExists(AUTHOR.book());
// ... simply means the same as this
notExists(selectOne().from(AUTHOR.book()));
// ... which is again convenience for
notExists(selectOne().from(AUTHOR).where(AUTHOR.ID.eq(BOOK.AUTHOR_ID)));

But why write all of this verbose syntax, when you can just write the implicit correlated path join?

For more information about ANTI JOIN, refer to the jOOQ manual.