An uncorrelated subquery is nested SQL whose result does not depend on values from the outer query. It behaves as though the outer query runs against the inner query’s results. This makes it useful for staging calculations that cannot be expressed as directly nested aggregates. section 4.3
Stage an aggregate, then compare
Compute average system output with a derived table: SELECT avg(sum_per_system) FROM ( SELECT sum(kWh) AS sum_per_system FROM v_power_per_day GROUP BY system_id ); A scalar uncorrelated subquery can instead filter readings against the overall maximum: SELECT read_on, power FROM readings WHERE power = (SELECT max(power) FROM readings); The comparison returns every reading tied at 133,900 W. For per-system maxima, join the outer table with an uncorrelated grouped subquery. section 4.3
Listing 4.2 A subquery being used to compute nested aggregates
SELECT avg(sum_per_system)
FROM (
SELECT sum(kWh) AS sum_per_system
FROM v_power_per_day
GROUP BY system_id
);
Runnable SQL demonstrates how a subquery stages grouped sums before applying the outer average. section 4.3
Uncorrelated subqueries may return multiple rows or one scalar value. Set comparisons extend them beyond equality: EXISTS checks presence, IN tests membership, ANY succeeds for one matching comparison, and ALL requires every comparison to succeed. section 4.3.1
