Comparing Rows with Correlated Subqueries

Hello! Last lesson, a non-correlated subquery supplied one average paid-order amount for every order to compare against. But a report might ask a more specific question: Which paid orders exceed the average paid-order amount in their own region? The benchmark must now depend on the region of the order being examined.

In this lesson, you’ll write a correlated subquery for that comparison, trace its result for individual orders, and check that the reported rows match the intended business definition.

Make the benchmark depend on the current row

A correlated subquery contains a reference to a column in its surrounding query. Think of the outer query considering a candidate order: the inner query uses that order’s region to calculate the appropriate average. This is a logical model for understanding the result, not a promise that PostgreSQL physically runs the inner query once per row; its optimizer may choose another execution plan.

Watch the correlated-subquery portion of Subquery in SQL by techTFQ. The example uses employee pay and departments rather than orders and regions, but the group-comparison problem is the same.

One distinction is worth making precise: scalar describes the size of the result—one value—whereas correlated describes a dependency on the outer query. A subquery that calculates one regional average for the current order is both scalar and correlated.

For a compact account of aliases and outer references, read the opening principles in Correlated Subqueries from Varsity Tutors.

Calculate a regional benchmark for each order

We’ll reuse the orders from the previous lesson. If your temporary table still exists, you can skip this setup. Otherwise, run it in PostgreSQL:

CREATE TEMP TABLE orders_stage (
    order_id integer PRIMARY KEY,
    region text,
    ordered_on date,
    status text,
    amount numeric(10, 2)
);

INSERT INTO orders_stage
    (order_id, region, ordered_on, status, amount)
VALUES
    (1, 'North', '2025-01-01', 'paid',      100.00),
    (2, 'North', '2025-01-01', 'paid',       50.00),
    (3, 'North', '2025-01-02', 'cancelled',  40.00),
    (4, 'North', '2025-01-02', 'pending',    70.00),
    (5, 'South', '2025-01-01', 'paid',       80.00),
    (6, 'South', '2025-01-01', 'cancelled',  20.00),
    (7, 'South', '2025-01-02', 'paid',        NULL),
    (8, 'South', '2025-01-03', 'pending',    30.00),
    (9, 'South', '2025-01-03', NULL,         10.00);

Before filtering anything, display the benchmark alongside each paid order. The two uses of orders_stage need different aliases: o identifies the outer candidate order; i identifies orders considered inside its regional average.

SELECT
    o.order_id,
    o.region,
    o.amount,
    ROUND((
        SELECT AVG(i.amount)
        FROM orders_stage AS i
        WHERE i.region = o.region
          AND i.status = 'paid'
    ), 2) AS region_paid_avg
FROM orders_stage AS o
WHERE o.status = 'paid'
ORDER BY o.order_id;

The crucial condition is i.region = o.region. For order 1, o.region is North, so the inner query averages the paid North amounts, 100 and 50. For order 5, it is South, so the inner query considers the paid South amounts, 80 and NULL. PostgreSQL’s AVG ignores the missing amount, giving South a recorded paid-order average of 80.

Notice that the inner query cannot run unchanged on its own: o.region gets its meaning from the outer query. That is the defining difference from last lesson’s global-average subquery. Also notice that i.status = 'paid' is separate from the outer o.status = 'paid'. The outer condition chooses candidate orders; the inner condition defines which orders contribute to their benchmark.

Filter with the row’s own group average

Now move the same scalar subquery into WHERE and compare it with the candidate order’s amount:

SELECT
    o.order_id,
    o.region,
    o.amount
FROM orders_stage AS o
WHERE o.status = 'paid'
  AND o.amount > (
      SELECT AVG(i.amount)
      FROM orders_stage AS i
      WHERE i.region = o.region
        AND i.status = 'paid'
  )
ORDER BY o.order_id;

Order 1 exceeds North’s average of 75. Order 2 does not. South’s order 5 is equal to its regional average of 80, so it fails the strict > comparison. Order 7 has a NULL amount; comparing NULL with 80 does not evaluate to true, so it is not returned.

This differs from the previous lesson’s global benchmark of approximately 76.67. Order 5, at 80, exceeds that global average but does not exceed its own region’s average. Removing i.region = o.region would silently change the business question even though the SQL would still run.

A quick independent check is to calculate the group benchmarks directly:

SELECT
    region,
    ROUND(AVG(amount), 2) AS paid_region_avg
FROM orders_stage
WHERE status = 'paid'
GROUP BY region
ORDER BY region;

This should return North at 75.00 and South at 80.00. Comparing those figures with the candidate rows is a useful way to validate a correlated query whose inner statement cannot be run unchanged in isolation.

Recognize the reusable pattern

The correlation condition chooses the related group; the aggregate and outer operator determine the comparison. For example, to find paid orders whose amount is the largest recorded paid amount in their region, replace AVG with MAX and > with =:

SELECT
    o.order_id,
    o.region,
    o.amount
FROM orders_stage AS o
WHERE o.status = 'paid'
  AND o.amount = (
      SELECT MAX(i.amount)
      FROM orders_stage AS i
      WHERE i.region = o.region
        AND i.status = 'paid'
  )
ORDER BY o.order_id;

This returns orders 1 and 5. If two paid orders shared a region’s maximum amount, both would be returned: equality to the group maximum does not break ties. The comparison still concerns each order and its own region, but it answers a different question from “above average.”

When adapting this pattern, check three decisions before trusting the output: which rows qualify as outer candidates, which rows belong in the inner benchmark, and what happens when a comparison value is missing. Here, a region with no recorded paid amounts would have a NULL average or maximum, so no amount would pass either comparison. If region itself can be NULL, ordinary i.region = o.region will not match those rows as one group; that case needs an explicit business rule.

Key takeaways

A correlated subquery references an outer-row value—in this case, o.region—so each candidate order is compared with the right group. A scalar aggregate such as AVG or MAX supplies the benchmark, while the outer comparison decides which rows to keep. Qualifying aliases and independently checking group aggregates make the logic easier to verify.

Next, you’ll learn how OVER and PARTITION BY place group-level calculations alongside rows using window functions, providing another way to express related-group analyses.