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));
Feedback
Do you have any feedback about this page? We'd love to hear it!