A nested query, or subquery, stages part of a SQL computation inside another statement. It separates operations that cannot be directly nested, such as averaging per-system sums. section 4.3
Choose the form that matches the result and comparison:
- Use an uncorrelated subquery when the inner query is independent of the outer query; it can return multiple rows or one scalar value. section 4.3
- Use a correlated subquery when the inner query refers to values from the current outer row, such as finding each system's maximum power. section 4.3
- Use scalar comparisons with exactly one-row results, or use IN, EXISTS, ANY, and ALL for set comparisons. section 4.3.1
- Prefer arg_min or arg_max for a single expression when they solve the task; use subqueries when selecting several expressions or other conditions. section 4.3
Compute and filter maximum readings
Stage per-system totals before averaging: SELECT avg(sum_per_system) FROM (SELECT sum(kWh) AS sum_per_system FROM v_power_per_day GROUP BY system_id); For per-system maxima, correlate the inner query with the outer row: 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; section 4.3
Listing 4.6 A subquery used with the EXISTS expression
.mode line SELECT * FROM VALUES (7), (11) s(v) #1 WHERE EXISTS (SELECT * FROM range(10) WHERE range = v);
Inspect an EXISTS query that keeps values having a match in the inner result. section 4.3.1
