Nested SELECT using the EXISTS operator

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

# The EXISTS operator for use in semi-joins or anti-joins

The EXISTS operator is rather independent and can stand any place where there may be a new condition:

• It may be placed right after a WHERE keyword
• It may be the right-hand-side of a boolean operator
• It may be placed right after a ON or HAVING keyword (although, this is less likely to be done...)

This is reflected by the fact that an EXISTS clause is usually created directly from the Factory:

Condition exists(Select<?> query);
Condition notExists(Select<?> query);

When you create such a Condition, it can then be connected to any other condition using AND, OR operators (see also the manual's section on Conditions). There are also quite a few convenience methods, where they might be useful. For instance in the org.jooq.Condition itself:

Condition andExists(Select<?> select);
Condition andNotExists(Select<?> select);
Condition orExists(Select<?> select);
Condition orNotExists(Select<?> select);

Or in the org.jooq.SelectWhereStep:

SelectConditionStep whereExists(Select<?> select);
SelectConditionStep whereNotExists(Select<?> select);

Or in the org.jooq.SelectConditionStep:

SelectConditionStep andExists(Select<?> select);
SelectConditionStep andNotExists(Select<?> select);
SelectConditionStep orExists(Select<?> select);
SelectConditionStep orNotExists(Select<?> select);

An example of how to use it is quickly given. Get all authors that haven't written any books:

SELECT *
  FROM T_AUTHOR
 WHERE NOT EXISTS (SELECT 1
                     FROM T_BOOK
                    WHERE T_BOOK.AUTHOR_ID = T_AUTHOR.ID)

create.select()
      .from(T_AUTHOR)
      .whereNotExists(create.selectOne()
            .from(T_BOOK)
            .where(T_BOOK.AUTHOR_ID.equal(T_AUTHOR.ID)));