Nested SELECT using the IN operator

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

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

In addition to a list of constant values, the IN operator in org.jooq.Field also supports a org.jooq.Select as an argument. This can be any type of select as discussed in the manual's section about Query types. However, you must ensure yourself, that the provided Select will only select a single Field.

Let's say you want to select books by authors born in 1920. Of course, this is possible with a plain JOIN as well, but let's say we want to use the IN operator. Then you have two possibilities:

SELECT *
  FROM T_BOOK
 WHERE T_BOOK.AUTHOR_ID IN (
           SELECT ID FROM T_AUTHOR
            WHERE T_AUTHOR.BORN = 1920)

-- OR:

SELECT T_BOOK.*
  FROM T_BOOK
  JOIN T_AUTHOR ON (T_BOOK.AUTHOR_ID = T_AUTHOR.ID
                AND T_AUTHOR.BORN    = 1920)

create.select()
      .from(T_BOOK)
      .where(T_BOOK.AUTHOR_ID.in(
          create.select(T_AUTHOR.ID).from(T_AUTHOR)
                .where(T_AUTHOR.BORN.equal(1920))));

// OR:

create.select(T_BOOK.getFields())
      .from(T_BOOK)
      .join(T_AUTHOR).on(T_BOOK.AUTHOR_ID.equal(TAuthor.ID)
                     .and(T_AUTHOR.BORN.equal(1920)));