26. Subqueries in SELECT/FROM/WHERE, derived tables

The previous lesson covered subqueries by correlation (whether they reference the outer query). This lesson covers them by position — where in a statement a subquery can legally appear — because each position has different rules about what shape of result is allowed.

In WHERE (and HAVING): already familiar

Every example so far. A subquery in WHERE can be scalar (compared with =, >, etc.), a row set (IN), or existence-only (EXISTS) — covered in the previous two lessons.

In SELECT: must be scalar

A subquery used as a column in SELECT must return exactly one row and one column per outer row — this is just the scalar-subquery rule from the previous lesson, positioned differently:

SELECT
    c.first_name,
    c.last_name,
    (SELECT count(*) FROM rental r WHERE r.customer_id = c.customer_id) AS rental_count
FROM customer c
ORDER BY rental_count DESC
LIMIT 5;

This correlated scalar subquery runs once per customer row, each time counting that specific customer's rentals. It's a legitimate pattern for "one extra computed column per row," though for anything beyond a single aggregate per row, a JOIN + GROUP BY (or a CTE, next lesson) usually reads more clearly and lets the planner optimize more freely.

In FROM: derived tables

A subquery in FROM — called a derived table or inline view — produces a full result set that the outer query then treats as if it were a real table. It must have an alias (Postgres requires this, even though the standard technically doesn't in every case):

SELECT rating, avg_rate
FROM (
    SELECT rating, avg(rental_rate) AS avg_rate
    FROM film
    GROUP BY rating
) AS rating_summary
WHERE avg_rate > 3.00
ORDER BY avg_rate DESC;

Why bother, when this specific example could just use HAVING directly? Because a derived table lets you build a multi-step pipeline: aggregate first, then filter/join/reference the aggregated result as a unit — useful once a single HAVING clause isn't expressive enough, e.g. filtering on a computed value that itself depends on a window function (Module 7) or another aggregate that can't simply go in HAVING.

Derived tables can be joined like any other table:

-- Compare each customer's total spend against the overall average spend,
-- computed once as a derived table and reused.
SELECT c.customer_id, c.first_name, spend.total, overall.avg_total
FROM customer c
JOIN (
    SELECT customer_id, sum(amount) AS total
    FROM payment
    GROUP BY customer_id
) AS spend ON spend.customer_id = c.customer_id
CROSS JOIN (
    SELECT avg(total) AS avg_total FROM (
        SELECT customer_id, sum(amount) AS total FROM payment GROUP BY customer_id
    ) AS per_customer
) AS overall
WHERE spend.total > overall.avg_total
ORDER BY spend.total DESC
LIMIT 10;

(That query is deliberately a little unwieldy — it's the motivating example for the next lesson. CTEs exist specifically to make exactly this shape of query readable.)

LATERAL — a brief preview

A derived table in FROM is normally uncorrelated — it can't reference other tables listed earlier in the same FROM clause, only the outer query in WHERE/SELECT (as the correlated-subquery lesson covered). LATERAL lifts that restriction, letting a FROM-clause subquery reference earlier tables in the same FROM list — genuinely useful for "top N per group" style queries. This course doesn't cover LATERAL in depth; it's flagged here only so the keyword doesn't blindside you when you meet it in the wild.

Check yourself

1. What result shape must a subquery in SELECT have, that a subquery in FROM does not need to have?
2. Why does Postgres require an alias on every derived table in FROM?
3. In your own words, why might a derived table be preferable to a plain HAVING clause for some filtering problems?