All the subqueries in 07-01 had something in common: they were computed once and were worth the same for every row. That's why none of them could answer the question left open at the end: which products beat the average of their category? That threshold isn't one, it's six —€6.18 for Food, €11.54 for Natural cosmetics, €8.90 for Drinks…— and each row needs its own.

A correlated subquery does exactly that: it looks outwards, at the row the outer query is examining at that instant, and it's recomputed for each one. The mental model changes (it stops being a constant and becomes a loop), the cost changes and a whole family of questions opens up: each customer's last order, the amount of their most expensive order, each product's most recent review.

1. How to recognise a correlated subquery
2. The mental model: one run per row
3. The canonical case: each product against its category's average
4. The variant: the most expensive in its category, and self-correlation
5. Four more GreenStore cases
6. Alias scope: who sees whom
7. The cost: N runs and what the planner does
8. When to use it and when it gives away that something else is missing
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

There's just one signal: inside the subquery there's an alias belonging to the outer query.

Copy that second query into psql and run it on its own:

That error is the diagnosis, not a problem: it confirms that the subquery depends on its surroundings and only makes sense inside the query containing it. It's 07-01's practical test, now seen from the other side.

Extend the logical-order diagram you've been building since module 2. What's new happens inside step 2: for every row reaching the WHERE, the correlated subquery runs in full and returns its value.

Conceptually it's a nested loop: the outer query walks its rows and, on each iteration, fires off the inner query. If you put three correlated subqueries in the SELECT and the outer table has 15 rows, that's 45 runs.

A trace of the first rows of products, with the subquery AVG(price) of its category:

Look at the first four rows: the same calculation repeated four times. That apparent waste is the reason for section 7, and also the reason modern planners rewrite many correlated subqueries.

8 products out of 20, and the comparison with 07-01 is very instructive: with the global average (€9.035) 9 products came out. They aren't 9 nor a subset of them:

The deodorant is cheap in absolute terms but expensive within its category; the bags are exactly the opposite. And the spirulina case is the funniest: it's the only Supplements product, so its category's average is its own price, and 16.40 > 16.40 is false. A product alone in its category can never beat its category's average.

Two writing details worth fixing:

• The expression is repeated in the SELECT and in the WHERE, exactly as happened with HAVING in 04-06. It's mandatory (the SELECT's aliases don't exist in the WHERE) and it's ugly. The clean solution is 10-02's CTEs.
• The aliases p and p2 are indispensable. Inside and outside it's the same products table, and without distinct aliases WHERE category_id = category_id would be a tautology: the subquery would ignore the category, would return the global average and the query would stop being correlated without raising any error. We'll come back to it in exercise 3.

Swapping AVG for MAX and > for =, the same structure answers another classic question:

6 rows, one per category. It's the greatest-n-per-group pattern, probably the most requested in all of analytical SQL.

Here the outer and the inner table are the same one, and that links straight back to 03-06's self join: just as there you joined employees with employees to get everybody's manager, here you compare products with products. The difference is that a self join produces pairs of rows and self-correlation produces a computed value for each row. When what you want is an aggregate per group, the correlated form usually reads better.

And a warning about ties: if two products of the same category shared the maximum price, both would come out, because both satisfy the equality. That's almost always what you want. If you needed exactly one per category, or the second one, or a full ranking, the right tool is no longer this one: it's the window functions (ROW_NUMBER(), RANK()) of 10-03.

A correlated subquery can also go in the SELECT, as a computed column. This query answers three questions at once:

All 15 customers, including the three who have never bought. That's an enormous difference from an INNER JOIN, which would have returned 12 rows: a subquery in the SELECT doesn't remove rows from the outer query; it returns a value or NULL, but the row is still there. It behaves like a LEFT JOIN without being one.

And notice the asymmetry in the last three rows: orders is 0 while the other two are NULL. It isn't an inconsistency, it's 04-04: COUNT over an empty set returns 0; MAX and SUM return NULL. If that column is going to feed a calculation, wrap it in COALESCE (06-04).

Fourth case: each product's most recent review. Here the correlation filters by product and LIMIT 1 trims:

9 products, the only ones with any review; the other 11 are excluded by the EXISTS (next lesson). And here a real flaw in this pattern shows up: there are two almost identical subqueries to get two columns from the same row, that is, twice the work. That's solved with LATERAL (07-04) or with window functions (10-03); with a single column the form above is perfectly reasonable.

The rule is asymmetric and has to be memorised:

The subquery sees the outer query's aliases. The outer query does NOT see the subquery's aliases.

Visibility goes from inside outwards, never the other way round. Think of each subquery as a function receiving the outer row as a parameter: it can read what it's handed, but what happens inside it isn't exported. If you need a column from the inner table in the result, the answer isn't a subquery: it's a JOIN (07-05).

Two practical consequences:

• When the inner and the outer table are the same one, the aliases are mandatory. products AS p outside, products AS p2 inside. Without them, WHERE category_id = category_id resolves inside the subquery and is always true.
• If a column name exists in both tables and you don't qualify it, the inner one wins. It's SQL's name resolution rule: the closest scope first, then the outer ones. It's an extremely subtle way of writing a query that "works" and answers a different question. Always qualify every column inside a correlated subquery.

Conceptually, a correlated subquery is a nested loop: N outer rows × 1 inner run. With products that's 20 runs; with a table of two million rows, two million. And if the subquery also does a JOIN, the work multiplies.

Conceptually. Because PostgreSQL doesn't necessarily run it that way. The planner rewrites many correlated subqueries into equivalent and far cheaper forms:

The last row is the one that matters: correlated subqueries in the SELECT are the ones that most often stay a loop. With 15 customers it makes no difference; with 15 million, a query with three subqueries in the SELECT can take minutes where a LEFT JOIN with GROUP BY takes seconds.

Really checking it —seeing the plan, measuring the time, knowing whether there was a semi-join or a loop— needs EXPLAIN ANALYZE, and that's lesson 08-05. Until then, keep the intuition and this rule: don't rewrite for performance without measuring, but be suspicious of correlated subqueries in the SELECT over large tables.

The two alarm signals are clear. If you repeat the same correlation three or four times in the SELECT, you're walking the same table three or four times to group by the same key: that's a GROUP BY. And if the words "ranking", "position", "the second one" or "running total" show up, no subquery will do it elegantly; those are window functions.

It returns exactly the same 8 rows as section 3. The difference is that each category's average is computed only once —six averages, in one pass— instead of twenty times. On top of that the expression stops being duplicated: it's named m.avg_price and used twice. That subquery in the FROM is called a derived table and it's 07-04's material.

It returns the same 15 rows as section 5's first two columns, with the same 0s and NULLs. A single pass over orders instead of 30 subqueries. When the correlated columns start piling up, this is the rewrite that's called for. The honest comparison of both forms —readability, rows, performance— is 07-05.

• Forgetting the aliases when the table is the same inside and outside. WHERE category_id = category_id is a tautology: the correlation disappears, the subquery computes the global average and there's no error at all.
• Not qualifying the columns inside the subquery. If the name exists in both tables, the inner scope wins and the query silently answers a different question.
• Trying to use an inner alias in the outer query. missing FROM-clause entry for table "p2". Visibility only goes from inside outwards.
• Running the subquery on its own to debug it. You can't: it'll give that same error. To test it, substitute the outer value by hand (WHERE p2.category_id = 1) and check it returns what you expect.
• Expecting a subquery in the SELECT to filter rows. It doesn't filter: it returns a value or NULL. All 15 customers still appear.
• Confusing COUNT's 0 with MAX or SUM's NULL. A customer with no orders has 0 orders and NULL in any other aggregated column.
• Writing a correlated scalar that returns several rows. more than one row returned by a subquery used as an expression: the correlation narrows things down, but doesn't guarantee uniqueness. Add an aggregate or an ORDER BY ... LIMIT 1.
• Tip: write the subquery with a fixed value first. Check that AVG(price) WHERE category_id = 1 gives 6.18 and then replace the 1 with p.category_id. Debugging both layers at once is needlessly hard.
• Tip: if you repeat the same correlation in two columns, move to LATERAL or to GROUP BY. Two identical subqueries to get two fields from the same row are twice the work for nothing.
• Tip: count how many times it'll run. Rows of the outer table × correlated subqueries. If the number makes you uncomfortable, consider section 8's rewrite before production does it for you.

Quality wants to know which products have an average rating above the overall average of all reviews. Write the query with a non-correlated subquery for the overall average and show id, product, number of reviews and average (two decimals), sorted by average descending and id.

Then answer: is any part of your query correlated? Why would the answer change if the question were "an average above the average of its category"?

Sales needs, for each customer who has bought, the amount of their last order (the one with the most recent date), along with their name and that date. Use correlated subqueries. Then say: what would happen if a customer had two orders on the same day, and how would you fix it?

A colleague has written this to get "the products above their category's average" and is puzzled that it returns the same 9 rows as 07-01's query with the global average, instead of 8:

1. Why does it return 9 rows and not 8?
2. Fix it.
3. What would the result have been if instead of category_id = category_id they'd written category_id = supplier_id? Explain the mechanism; the exact number isn't needed.

Solution 1

4 products out of the 9 reviewed beat the overall average, which is 4.0833… (49 points across 12 reviews). Left out are the detergent and the toothbrush (4.00), the tomato and the bags (3.00) and the kombucha (2.00).

There's no correlation anywhere: the subquery SELECT AVG(rating) FROM reviews mentions nothing from outside, runs once and gives a number. It's the same pattern as 07-01's average order value. If the question were "above the average of its category", the subquery would have to filter by the category of the current group's product —WHERE p2.category_id = p.category_id— and would become correlated, evaluated once per group.

Solution 2

12 rows. There are three levels of nesting and two different correlations against c.id. Compare the result with section 5's: Lucía's last order is worth €33.40 while her most expensive order is worth €42.10; Camille's is €22.60 against €48.27. They're different questions and it's worth not mixing them up in a report.

If a customer had two orders on the same day, the amount subquery would add up both and return an inflated total (it wouldn't error, because the GROUP BY isn't there and SUM aggregates everything it's given). The fix is to break the tie with the primary key: instead of filtering by order_date = MAX(...), filter by o.id = (SELECT o2.id FROM orders AS o2 WHERE o2.customer_id = c.id ORDER BY o2.order_date DESC, o2.id DESC LIMIT 1). Any "the latest" based only on a date with no time is fragile; always add a deterministic tie-breaker.

Solution 3

1. Because category_id = category_id resolves entirely inside the subquery: there's no alias distinguishing the outer table from the inner one, so both occurrences refer to the inner products. The condition is "a column equal to itself", true for all 20 rows, and the subquery ends up returning the global average, €9.035 — hence the 9 rows. The query isn't correlated at all, even though it looks like it, and that's the flaw: the category filter is never applied. It's a textbook silent error.

2. The fix is section 3's: distinct aliases inside and outside.

8 rows, section 3's.

3. category_id = supplier_id doesn't correlate either: it compares two columns of the same inner table. The subquery would return the average of the products whose category happens to match their supplier numerically —a condition with no business meaning whatsoever but perfectly valid— and that single number would apply to all 20 rows. It's the worst of the cases: a query that raises no error, looks correlated, and answers a question nobody asked. Hence the rule of always qualifying columns.

Correlation changes the nature of a subquery:

• A correlated subquery references an alias of the outer query; it can't be run on its own (missing FROM-clause entry) and it's evaluated once for every candidate row, like a nested loop.
• The canonical case is comparing each row with an aggregate of its own group: 8 products beat their category's average, against the 9 that beat the global average — and they aren't the same ones, because the deodorant is expensive for Personal hygiene and the bags are cheap for Sustainable home.
• The = MAX(...) variant gives the most expensive in each category (6 rows, one per category), the greatest-n-per-group pattern, sibling of self-correlation and of 03-06's self join.
• In the SELECT it works as a computed column and doesn't remove rows: all 15 customers are still there, with 0 in COUNT and NULL in MAX and SUM.
• Visibility is asymmetric: the subquery sees the outer aliases, the outer query doesn't see the inner ones. When the table is the same, distinct aliases are mandatory: without them the correlation silently evaporates.
• Conceptually it costs N runs. PostgreSQL rewrites many as semi-joins or anti-joins, but correlated subqueries in the SELECT usually stay a loop; really measuring it is 08-05.
• And you can recognise when it isn't the tool: several repeated correlations call for a LEFT JOIN with GROUP BY, and any ranking calls for window functions (10-03).

In the next lesson, EXISTS and NOT EXISTS, you'll see the purest form of correlated subquery: one that returns no value at all, it only answers yes or no. With it you'll finally write "customers who have never bought" without a LEFT JOIN, you'll discover why NOT EXISTS is safe with nulls and NOT IN isn't, and you'll solve the classic problem of relational division: which customers have bought from every category.