Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →An aggregate written inside a subquery can belong to an outer query level when its arguments refer only to columns from that outer level. This is an aggregate-scope rule, not a statement about how the database executes the query. Keeping aggregate ownership separate from correlation and execution strategy makes nested SQL easier to read and debug.
What is an aggregate with an outer reference in SQL?
An outer reference is a column reference in an inner query that is supplied by a query block above it. For example, EnterpriseDB WarehousePG defines a correlated subquery as a SELECT whose WHERE clause or target list refers to its parent query. In this example, the inner query uses the outer row’s t1.y value:
As an Amazon Associate I earn from qualifying purchases.
SELECT * FROM t1
WHERE t1.x > (SELECT MAX(t2.x) FROM t2 WHERE t2.y = t1.y);The reference to t1.y makes the subquery correlated. But MAX(t2.x) aggregates an inner-query column, so this example does not demonstrate an aggregate owned by the outer query. Correlation and aggregate ownership are related scope questions, but they are not the same thing. WarehousePG v7.4: Defining Queries
Why can an aggregate inside a subquery belong to the outer query?
PostgreSQL’s value-expression documentation describes the key rule: an aggregate in a subquery is normally computed over that subquery’s rows. If the aggregate’s arguments—and its FILTER clause, if present—contain only variables from an outer query level, the aggregate belongs to the nearest such outer level instead. The aggregate expression is then an outer reference from the subquery’s point of view. PostgreSQL 11: Value Expressions
#1 Best Overall
“Constant” in this context is local, not global. During one evaluation of the subquery, the outer-level aggregate value is fixed: the subquery reads it as a value supplied from outside. Its value can still differ for another outer group or row, because the owning query level’s variables may differ.
How to identify the aggregate’s owning query level
When a nested aggregate looks surprising, trace its inputs before reasoning about where its text appears. PostgreSQL’s rule can be applied with this sequence:
- List every column reference in the aggregate’s arguments and, if present, its
FILTERexpression. - For each reference, identify the query block that supplies the column.
- Find the nearest outer query level that supplies all those references. If the aggregate’s inputs are exclusively from that level, the aggregate belongs there under PostgreSQL’s documented rule.
- Check whether the aggregate is legal in a clause of its owning SELECT, rather than judging only by the clause where the expression is written in the subquery.
This distinction matters because an aggregate expression may appear in the result list or HAVING clause of its owning SELECT, but not in clauses such as WHERE, which are logically evaluated before aggregate results are formed. For an aggregate whose text is nested, apply that placement restriction at the level that owns the aggregate. PostgreSQL 11: Value Expressions
Does correlation mean the subquery runs once for every outer row?
No. Correlation describes a dependency: the inner query refers to a value from an outer query. It does not, by itself, specify the execution plan. WarehousePG documentation says its optimizer can unnest many correlated subqueries into joins, while some forms—including select-list correlated subqueries and subqueries connected by OR conditions—may run for each outer row. Those are WarehousePG-specific descriptions, not universal rules for database engines. WarehousePG v7.4: Defining Queries
For performance questions, inspect the plan on the database and release that will run the query. WarehousePG recommends EXPLAIN or EXPLAIN ANALYZE to examine plans and identify candidate rewrites. A plan is evidence about that query in that environment; correlation alone does not establish that a query is slow or that a rewrite will be faster.
When is rewriting an aggregate subquery appropriate?
WarehousePG documents a rewrite for an aggregate in a correlated subquery: compute COUNT(DISTINCT T2.z) grouped by the correlated key, then join those results back. The documented example is limited to an equijoin correlation condition. Do not treat that pattern as a mechanical substitution: confirm that the grouped result and join preserve the original query’s semantics, including what happens when there is no matching group. WarehousePG v7.4: Defining Queries
Does every database resolve nested aggregates the same way?
No general cross-database conclusion follows from the PostgreSQL rule. MySQL 8.4.9’s server-source documentation discusses how nested-query aggregates can be associated with different query blocks, potentially producing different interpretations, and describes resolution in relation to nesting and clause validity. It is implementation documentation, including discussion of ANSI mode—not a promise that every SQL product accepts or resolves the same query forms identically. MySQL 8.4.9 server source: sql/item_sum.h
PostgreSQL 11 is the explicitly versioned source for the aggregate-ownership rule described here. Check the documentation for the specific database product and release you use when relying on nested aggregate behavior.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
