liveBook · Manning

Correlated subquery

As explained in DuckDB in Action

A correlated subquery is a subquery that references fields from its outer query and is evaluated in relation to each outer row.

A correlated subquery references a field from its outer query, so its result can change for each outer row. This contrasts with an uncorrelated subquery, which runs independently and produces one shared result. section 4.3

Maximum power per system

SELECT system_id, read_on, power FROM readings r1 WHERE power = ( SELECT max(power) FROM readings r2 WHERE r2.system_id = r1.system_id ) ORDER BY ALL; The inner query computes a maximum separately for each outer row’s system_id, returning every reading tied for that system’s maximum.

Subqueries also support set comparisons:

  • Use EXISTS to test whether a related result exists. section 4.3.1
  • Use IN for equality against values returned by a subquery. section 4.3.1
  • Use ANY or ALL for inequality or universal comparisons across returned values. section 4.3.1
SELECT avg(sum(kWh)) FROM v_power_per_day GROUP BY system_id

Compare staged aggregation with the correlated pattern when a calculation must vary by outer-row values. section 4.3

How DuckDB in Action covers it

How deep it goes
Explains correlation through a scalar maximum query and contrasts it with uncorrelated and set-comparison subqueries.
Examples
Demonstrates per-system maximum readings and contrasts them with staged aggregates and an equivalent grouped join.
What it assumes
Assumes SQL queries, WHERE conditions, aggregate functions, and the distinction between outer and inner query scopes.
What you can do after
Apply it via SQL to return rows matching an aggregate computed separately for each outer-row group.
How it connects
Connects correlated subqueries with aggregate functions, scalar subqueries, WHERE conditions, set comparisons, and joins.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.