Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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.
References to this page
Do you have any feedback about this page? We'd love to hear it!