Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Scalar subqueries
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)
For example:
SELECT AUTHOR.ID, ( SELECT count(*) FROM AUTHOR ) AS authors FROM AUTHOR
create.select( AUTHOR.ID, field(selectCount().from(AUTHOR)).as("authors")) .from(AUTHOR) .fetch();
Correlated subqueries
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
Feedback
Do you have any feedback about this page? We'd love to hear it!