ANTI JOIN is a really helpful operator from relational algebra. Regrettably, solely few dialects assist it when it comes to SQL syntax, as we’ve written earlier. In jOOQ, you’ll be able to 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 rather a lot to sort. Since jOOQ 3.19, to-many be a part of paths can be found and can be utilized right here, as a substitute:
This does precisely the identical factor as the unique assertion. However what for those who choose working with NOT EXISTS (or EXISTS for SEMI JOIN semantics)? In any case, the phrases ANTI JOIN and SEMI JOIN is probably not what folks consider day-after-day, and as such, won’t be too readable.
You may nonetheless use be a part of paths like proven earlier than! Simply write this, as a substitute:
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’ll be able to simply write the implicit correlated path be a part of?
For extra details about ANTI JOIN, confer with the jOOQ handbook.
