The jOOQ User Manual SQL building Column expressions Scalar subqueries

previous : next

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.

The jOOQ User Manual SQL building Column expressions Scalar subqueries

previous : next