New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6
Nested SELECTs
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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"))));
SELECT nested.* FROM (
SELECT AUTHOR_ID, count(*) books
FROM BOOK
GROUP BY AUTHOR_ID
) nested
ORDER BY nested.books DESC
Table<?> nested =
create.select(BOOK.AUTHOR_ID, count().as("books"))
.from(BOOK)
.groupBy(BOOK.AUTHOR_ID).asTable("nested");
create.select(nested.getFields())
.from(nested)
.orderBy(nested.getField("books"));
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<?> 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));
