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

A SELECT statement can appear almost anywhere a table expression can. Such a "nested SELECT" is often called a "derived table". Apart from many convenience methods accepting org.jooq.Select objects directly, a SELECT statement can always be transformed into a org.jooq.Table object using the asTable() method.

Example: Scalar subquery

SELECT *
  FROM BOOK
 WHERE BOOK.AUTHOR_ID = (
 		SELECT ID
          FROM AUTHOR
         WHERE LAST_NAME = 'Orwell')
 
create.select()
      .from(BOOK)
      .where(BOOK.AUTHOR_ID.eq(create
             .select(AUTHOR.ID)
             .from(AUTHOR)
             .where(AUTHOR.LAST_NAME.eq("Orwell"))))
      .fetch();
 

SELECT nested.* FROM (
      SELECT AUTHOR_ID, count(*) books
        FROM BOOK
    GROUP BY AUTHOR_ID
) nested
ORDER BY nested.books DESC
 
Table<Record> nested =
    create.select(BOOK.AUTHOR_ID, count().as("books"))
          .from(BOOK)
          .groupBy(BOOK.AUTHOR_ID).asTable("nested");

create.select(nested.fields())
      .from(nested)
      .orderBy(nested.field("books"))
      .fetch();
 



SELECT LAST_NAME, (
      SELECT COUNT(*)
       FROM BOOK
      WHERE BOOK.AUTHOR_ID = AUTHOR.ID) books
    FROM AUTHOR
ORDER BY books DESC
 
// The type of books cannot be inferred from the Select<?>
Field<Object> books =
    create.selectCount()
          .from(BOOK)
          .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
          .asField("books");

create.select(AUTHOR.ID, books)
      .from(AUTHOR)
      .orderBy(books, AUTHOR.ID))
      .fetch();
The jOOQ Logo