Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
EXISTS predicate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Slightly less intuitive, yet more powerful than the previously discussed IN predicate is the EXISTS predicate, that can be used to form semi-joins or anti-joins. With jOOQ, the EXISTS predicate can be formed in various ways:
- From the DSL, using static methods. This is probably the most used case
- From a conditional expression using convenience methods attached to boolean operators
- From a SELECT statement using convenience methods attached to the where clause, and from other clauses
An example of an EXISTS predicate can be seen here:
EXISTS (SELECT 1 FROM BOOK
WHERE AUTHOR_ID = 3)
NOT EXISTS (SELECT 1 FROM BOOK
WHERE AUTHOR_ID = 3)
exists(create.selectOne().from(BOOK)
.where(BOOK.AUTHOR_ID.eq(3)));
notExists(create.selectOne().from(BOOK)
.where(BOOK.AUTHOR_ID.eq(3)));
Note that in SQL, the projection of a subselect in an EXISTS predicate is irrelevant. To help you write queries like the above, you can use jOOQ's selectZero() or selectOne() DSL methods
Performance of IN vs. EXISTS
In theory, the two types of predicates can perform equally well. If your database system ships with a sophisticated cost-based optimiser, it will be able to transform one predicate into the other, if you have all necessary constraints set (e.g. referential constraints, not null constraints). However, in reality, performance between the two might differ substantially. An interesting blog post investigating this topic on the MySQL database can be seen here:
https://blog.jooq.org/not-in-vs-not-exists-vs-left-join-is-null-mysql/
Dialect support
This example using jOOQ:
exists(select(asterisk()).from(BOOK))
Translates to the following dialect specific expressions:
-- All dialects EXISTS ( SELECT * FROM BOOK )
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!