Subqueries

"Orders above the average" cannot be written in one pass. You need the average before you can compare anything to it, and WHERE amount > AVG(amount) is rejected — an aggregate cannot appear in WHERE, because WHERE runs before the rows are aggregated.

A scalar subquery solves it. Put a whole SELECT in brackets where a value would go; it runs first, returns exactly one row and one column, and its result is used as that value.

WHERE amount > (SELECT AVG(amount) FROM orders WHERE status = 'paid')

The inner query is completely independent — it does not mention the outer one, so it is evaluated once and the number reused for every row.

Your task: return order_id and amount for every paid order worth more than the average paid order, ordered by order_id.

The second place a subquery can sit is the SELECT list, where it produces a column. The rule is stricter here: it must return one row and one column, for every row of the outer query — more than one row and Postgres raises more than one row returned by a subquery used as an expression.

This one is correlated: it mentions u from the outer query, so it is re-evaluated for each user rather than once.

SELECT u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) AS orders
FROM users u

It is a genuine alternative to LEFT JOIN … GROUP BY, and it has one real advantage: COUNT over no rows is 0, so users who never ordered come back as 0 with no COALESCE and no risk of counting a padded row.

Your task: return every user's name and their number of orders as orders, using a subquery in the SELECT list, ordered by u.user_id.

Back in module 1-02, IN took a list you typed: WHERE country IN ('UK', 'US'). It will just as happily take a list a query produces.

WHERE user_id IN (SELECT user_id FROM orders)

The subquery must return exactly one column; it may return any number of rows, including none. This is a membership test — "is this value among those?" — and it does not widen the result or duplicate anything, which is a real advantage over a join when you want no columns from the other table.

Your task: return user_id and name for every user who has placed at least one order, using IN, ordered by user_id.

This is the most notorious silent bug in SQL, it is live in this database, and it produces an empty result rather than an error.

"Which users have never referred anybody" looks like a job for NOT IN against referred_by. Run it and you get zero rows. The honest answer is six.

WHERE user_id NOT IN (SELECT referred_by FROM users)   -- 0 rows

referred_by is NULL for four users, so the list contains a NULL. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL — and that last comparison is UNKNOWN, never false. The whole AND chain can therefore never be true, for any row. The three-valued logic of module 1-05, arriving where it does real damage.

Two fixes: exclude the NULLs inside the subquery, or use NOT EXISTS, which is immune. Plain IN is unaffected — only the negation breaks.

Your task: return user_id and name for every user who has referred nobody, using NOT IN with the NULLs excluded, ordered by user_id.

EXISTS takes a subquery and returns true if it produced any row at all. It does not care what is in those rows, which is why the convention is to select the literal 1 — writing SELECT * is equally valid and slightly misleading, because nothing is ever read.

WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.user_id AND o.status = 'paid')

It is nearly always correlated — the inner query references the outer row, which is what makes it a question about this user rather than about the table in general.

Compared with IN, it handles multi-column conditions naturally, and it can stop at the first matching row rather than building a whole list.

Your task: return user_id and name for every user with at least one paid order, using EXISTS, ordered by user_id.

NOT EXISTS is true when the subquery returns nothing. It is the third way of asking the question that 3-02 answered with a LEFT JOIN and 3-04 answered with EXCEPT.

Its decisive advantage over NOT IN is that it is immune to the NULL trap. It never compares values, so there is no comparison to come back UNKNOWN — it only asks whether rows came back. When negating, this is the default worth reaching for.

Your task: return user_id and name for every user who has never placed an order, using NOT EXISTS, ordered by user_id.

The fourth place, and the most powerful. A subquery in FROM is a derived table: a whole result set, used exactly as though it were a table.

It solves the problem of wanting to filter on something you had to compute first. You cannot put an aggregate in WHERE, and HAVING only tests the groups of the current query — so aggregate in an inner query, then treat its output as rows.

FROM (SELECT u.name, SUM(o.amount) AS spent
      FROM users u JOIN orders o ON o.user_id = u.user_id
      WHERE o.status = 'paid'
      GROUP BY u.user_id, u.name) t
WHERE t.spent > 200

Postgres requires the alias — the t at the end. Leave it off and you get subquery in FROM must have an alias. Everything the inner query named is then reached through it.

Your task: return name and spent for every user whose total paid spend is over 200, using a derived table, ordered by spent descending.

A correlated subquery re-runs for every outer row, and can see that row's values. That makes a question expressible which is genuinely awkward otherwise: compare each row against an average computed from only its own group.

WHERE o.amount > (SELECT AVG(o2.amount)
                  FROM orders o2
                  JOIN users u2 ON u2.user_id = o2.user_id
                  WHERE u2.country = u.country)

The inner query has its own aliases — o2, u2 — precisely so that u.country unambiguously means the outer row's country. Reuse the outer names inside and the correlation silently disappears.

Correlated subqueries are the honest, portable way to write this. Part 4 does it again with window functions, which are usually faster and occasionally clearer.

Your task: return order_id, country and amount for every order worth more than the average order in that order's own country, ordered by order_id.

Two quantifiers that turn a comparison into a statement about a whole list. They are rarely necessary — an aggregate usually says the same thing — but they read well and they turn up in other people's queries.

One caution, familiar by now: an empty subquery makes ALL vacuously true and ANY false, and NULLs inside the list can make either UNKNOWN. The MAX/MIN form is usually easier to reason about.

Your task: return name and price for every product costing more than every product in the addon category, using ALL, ordered by price descending.