Other types of nested SELECT

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

# Comparison with single-field SELECT clause

If you can ensure that a nested SELECT will only return one Record with one Field, then you can test for equality. This is how it is done in SQL:

SELECT *
  FROM T_BOOK
 WHERE T_BOOK.AUTHOR_ID = (
 		SELECT ID
          FROM T_AUTHOR
         WHERE LAST_NAME = 'Orwell')

create.select()
      .from(T_BOOK)
      .where(T_BOOK.AUTHOR_ID.equal(create
             .select(T_AUTHOR.ID)
             .from(T_AUTHOR)
             .where(T_AUTHOR.LAST_NAME.equal("Orwell"))));

More examples like the above can be guessed from the org.jooq.Field API, as documented in the manual's section about Conditions. For the = operator, the available comparisons are these:

Condition equal(Select<?> query);
Condition equalAny(Select<?> query);
Condition equalAll(Select<?> query);

# Selecting from a SELECT - SELECT acts as a Table

Often, you need to nest a SELECT statement simply because SQL is limited in power. For instance, if you want to find out which author has written the most books, then you cannot do this:

  SELECT AUTHOR_ID, count(*) books
    FROM T_BOOK
GROUP BY AUTHOR_ID
ORDER BY books DESC

Instead, you have to do this (or something similar). For jOOQ, this is an excellent example, combining various SQL features into a single statement. Here's how to do it:

SELECT nested.* FROM (
      SELECT AUTHOR_ID, count(*) books
        FROM T_BOOK
    GROUP BY AUTHOR_ID
) nested
ORDER BY nested.books DESC

Table<Record> nested =
    create.select(T_BOOK.AUTHOR_ID, count().as("books"))
          .from(T_BOOK)
          .groupBy(T_BOOK.AUTHOR_ID).asTable("nested");

create.select(nested.getFields())
      .from(nested)
      .orderBy(nested.getField("books"));

You'll notice how some verbosity seems inevitable when you combine nested SELECT statements with aliasing.

# Selecting a SELECT - SELECT acts as a Field

Now SQL is even more powerful than that. You can also have SELECT statements, wherever you can have Fields. It get's harder and harder to find good examples, because there is always an easier way to express the same thing. But why not just count the number of books the really hard way? :-) But then again, maybe you want to take advantage of Oracle Scalar Subquery Caching

  SELECT LAST_NAME, (
      SELECT COUNT(*)
       FROM T_BOOK
      WHERE T_BOOK.AUTHOR_ID = T_AUTHOR.ID) books
    FROM T_AUTHOR
ORDER BY books DESC

// The type of books cannot be inferred from the Select<?>
Field<Object> books =
    create.selectCount()
          .from(T_BOOK)
          .where(T_BOOK.AUTHOR_ID.equal(T_AUTHOR.ID))
          .asField("books");
create.select(T_AUTHOR.ID, books)
      .from(T_AUTHOR)
      .orderBy(books, T_AUTHOR.ID));