Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A scalar subquery is a subquery that produces a scalar value, i.e. one row and one column. Such values can be used as ordinary column expressions. Syntactically, any
Select<Record1<?>> type qualifies as a scalar subquery, irrespective of content and whether it is "correlated".
There are mostly 3 ways of creating scalar subqueries in jOOQ
- Type safe wrapping using DSL.field(Select)
- Type unsafe wrapping using Select.asField()
- Through convenience methods, such as Field.eq(Select)
SELECT AUTHOR.ID, ( SELECT count(*) FROM AUTHOR ) AS authors FROM AUTHOR
create.select( AUTHOR.ID, field(selectCount().from(AUTHOR)).as("authors")) .from(AUTHOR) .fetch();
A "correlated" subquery is a subquery (scalar or not) whose execution depends on the query that it is embedded in. It acts as a function taking the current row as an input argument.
SELECT AUTHOR.ID, ( SELECT count(*) FROM BOOK WHERE BOOK.AUTHOR_ID = AUTHOR.ID ) AS books FROM AUTHOR
create.select( AUTHOR.ID, field(selectCount() .from(BOOK) .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))) .from(AUTHOR) .fetch();
In the above example, the subquery counts the number of books for each author from the outer query.