All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6

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:

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:
http://blog.jooq.org/2012/07/27/not-in-vs-not-exists-vs-left-join-is-null-mysql/

The jOOQ Logo