Mastering ANTI JOIN in SQL with jOOQ: A Full Information

ANTI JOIN is a really helpful operator from relational algebra. Regrettably, solely few dialects assist it by way of SQL syntax, as we’ve written earlier. In jOOQ, you may write it as follows:

In case your RDBMS helps this natively (e.g. ClickHouse, Databricks), then it’s rendered as such. In any other case, jOOQ will translate this to:

However even with the comfort syntax, it’s lots to kind. Since jOOQ 3.19, to-many be a part of paths can be found and can be utilized right here, as an alternative:

This does precisely the identical factor as the unique assertion. However what when you favor working with NOT EXISTS (or EXISTS for SEMI JOIN semantics)? In spite of everything, the phrases ANTI JOIN and SEMI JOIN is probably not what folks consider on daily basis, and as such, won’t be too readable.

You’ll be able to nonetheless use be a part of paths like proven earlier than! Simply write this, as an alternative:

Doesn’t this learn very naturally?

“Get all authors for which there doesn’t exist a ebook”

I.e. all authors that haven’t written any books but. This can be a mixture of comfort the place:

However why write all of this verbose syntax, when you may simply write the implicit correlated path be a part of?

For extra details about ANTI JOIN, seek advice from the jOOQ handbook.