The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →An aggregate written inside a subquery can belong to an outer query level when every column used by its argument—and by its FILTER clause, if present—comes from that outer level. In that case, the aggregate is evaluated by the nearest query level that supplies all of those references, and the resulting value is fixed for one evaluation of the subquery. It can still vary for different outer groups or rows.
What is an aggregate with an outer reference in SQL?
SQL scope has three separate ideas that are easy to conflate:
- Correlation: an inner query refers to a column from a parent query.
- Aggregate ownership: the query level that computes an aggregate such as
MAX,COUNT, orSUM. - Execution strategy: whether the database runs the subquery repeatedly, unnests it into a join, or applies another plan transformation.
The text location of an aggregate does not always determine its owner. PostgreSQL’s value-expression documentation states that an aggregate in a subquery normally operates on that subquery’s rows. The exception is an aggregate whose argument references only variables from an outer query level. That aggregate belongs to the nearest outer level supplying all of its references.
Why does an aggregate inside a subquery refer to the outer query?
Consider the references inside the aggregate, not just the query block containing its text. If every reference in the aggregate argument is bound to an outer query, the aggregate is an outer-level expression.
#1 Best Overall
- Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
- Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
- Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
- Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
- 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.
An aggregate over inner rows
SELECT t1.id
FROM t1
WHERE t1.x > (
SELECT MAX(t2.x)
FROM t2
WHERE t2.y = t1.y
);This is a correlated subquery: its predicate uses t1.y from the parent query. However, MAX(t2.x) references t2.x, an inner-level column, so the MAX belongs to the subquery and aggregates rows from t2. The outer reference is in the subquery’s filter, not in the aggregate argument.
An aggregate owned by the outer level
In a different shape, an aggregate written in a subquery can use only outer columns:
SELECT t1.group_id,
(SELECT SUM(t1.amount)) AS group_total
FROM t1
GROUP BY t1.group_id, t1.amount;Here, SUM(t1.amount) has no inner-level argument. Under PostgreSQL’s rule, the aggregate is owned by the nearest outer level that supplies t1.amount, subject to that level’s grouping and aggregate rules. The subquery expression is therefore an outer reference to the aggregate result rather than an aggregate over rows produced by the subquery.
Rank #2
- 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
- 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
- Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
- 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
- What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.
The exact legality of a query still depends on grouping and on the clause in which the owning aggregate appears. The example is intended to show scope, not to recommend a particular report query.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
What does “constant” mean in this context?
PostgreSQL describes the outer-owned aggregate expression as acting like a constant within one evaluation of the subquery. “Constant” is local, not global:
- During one evaluation for a particular outer row or outer group, the referenced aggregate value does not change as the subquery expression is considered.
- For another outer row or group, the owning aggregate may produce a different value.
- The value is not a session-wide constant and is not necessarily the same for the entire statement.
The query level that supplies the aggregate’s variables determines the scope of that fixed value.
Rank #3
- Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
- Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
- Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
- Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
- What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.
Where may the owning aggregate appear?
PostgreSQL permits an aggregate expression in the result list or HAVING clause of its owning SELECT. It cannot generally be placed in a clause such as WHERE at that owning level, because filtering logically precedes formation of aggregate results.
For a nested expression, apply this test to the aggregate’s owner, not merely to the query block where the characters appear. An aggregate typed inside a subquery may therefore be subject to the outer query’s clause restrictions.
Recommended Free Tools
How can you determine aggregate ownership?
- List every reference inside the aggregate argument. Include expressions inside a
FILTER (WHERE ...)clause, because those references also affect scope. - Bind each reference to a query block. Mark whether it belongs to the current subquery, its parent, or another outer level.
- Find the nearest level that supplies all references. That level owns the aggregate when no inner-level reference is present.
- Check grouping and clause legality at that level. Test the aggregate against the owning
SELECT‘s result-list,HAVING,WHERE, and grouping rules. - Only then inspect the execution plan. Scope and ownership are semantic questions; the plan describes how the engine chooses to execute them.
Does correlation mean the subquery runs once per outer row?
No. Correlation describes a reference from an inner query to an outer query. It does not by itself specify the physical execution method.
Rank #4
- Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
- Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
- Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
- Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
- Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft
EnterpriseDB WarehousePG documentation says many correlated subqueries can be unnested into joins, while some forms may be executed for each outer row. It specifically identifies select-list correlated subqueries and correlated subqueries connected by OR conditions as forms that may retain per-row execution. These are WarehousePG-specific observations, not a universal rule for every database product or release.
Use the relevant engine’s plan tools—such as EXPLAIN or EXPLAIN ANALYZE in WarehousePG—to see whether a query was transformed and to locate expensive operations. Do not infer performance from the presence of correlation alone.
When can a correlated aggregate be rewritten as a join?
WarehousePG documents a rewrite pattern for an aggregate correlated subquery: compute the aggregate grouped by the correlation key, then join that derived result back to the outer relation. A schematic form is:
Best Value
- 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
- Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
- Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
- HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
- What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.
-- Correlated form (shape varies by query)
SELECT ...
FROM t1
WHERE ... = (
SELECT COUNT(DISTINCT t2.z)
FROM t2
WHERE t2.key = t1.key
);
-- Join-based form
SELECT ...
FROM t1
JOIN (
SELECT key, COUNT(DISTINCT z) AS z_count
FROM t2
GROUP BY key
) AS a ON a.key = t1.key
WHERE ... = a.z_count;That documented rewrite is limited to an equijoin correlation condition. Check null handling, duplicate preservation, empty groups, additional predicates, and comparison operators before treating the two forms as equivalent. Validate the actual plan and result set on the target engine and release.
How do other database systems resolve nested aggregates?
Nested aggregate resolution is not specified identically by every product. MySQL 8.4.9 server-source documentation discusses the difficulty of identifying which query block owns a set function when nested queries permit multiple interpretations. It describes resolving the aggregate location according to nesting and clause validity, with ANSI mode included in its implementation discussion.
Use PostgreSQL’s rule as a PostgreSQL rule, and MySQL’s notes as an implementation-specific caution. Do not assume that a query accepted by one product has the same ownership, legality, or result in another. Always check the documentation for the exact engine and release.
Common mistakes to avoid
- Calling every nested aggregate an inner aggregate: inspect its column bindings first.
- Treating correlation as an execution promise: an optimizer may decorrelate, join, materialize, or repeatedly execute the subquery.
- Reading “constant” as global: the value is fixed only within one subquery evaluation at the owning outer level.
- Checking clause legality in the textual block only: apply aggregate restrictions where the aggregate is owned.
- Applying a rewrite mechanically: the WarehousePG example assumes an equijoin correlation, and semantic equivalence must be established for the real query.
Frequently Asked Questions
How can an aggregate inside a subquery be evaluated at the outer query level?
If every column reference in the aggregate argument and its FILTER expression comes from an outer query level, PostgreSQL assigns the aggregate to the nearest level that supplies those references. The subquery then sees the aggregate result as an outer reference.
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 →Is a correlated subquery always evaluated once for every outer row?
No. Correlation is a scope relationship, not an execution plan. Depending on the engine, release, query shape, and data, the optimizer may unnest the subquery into a join or retain repeated execution. Inspect the plan.
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.
