Subqueries — a query inside a query

Sometimes a question genuinely needs the answer to a smaller question first — "orders above the average amount" needs the average computed before it can filter. A subquery is a SELECT nested inside another SELECT, used to answer exactly that shape of question.

Intermediate

4 min read

The problem: a filter that depends on a computed value

-- "Find orders above the average order amount" — but the average isn't known
-- until it's computed from the very table being filtered
SELECT * FROM orders WHERE amount > /* the average amount, somehow */;

WHERE amount > 200 works fine when the comparison value is already known — but "above average" requires computing the average first, from the same table being queried. A single flat SELECT has no way to reference a value it hasn't computed yet within its own WHERE clause; this is exactly the shape a subquery exists to handle.

A subquery in WHERE: computing a value to filter against

SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

The parenthesized SELECT AVG(amount) FROM orders is the subquery (or inner query) — it runs first, produces a single value (the average), and the outer query then uses that value in its WHERE clause exactly as if it had been typed in directly. This is the aggregation lesson's AVG() combined with filtering, solving a question a single flat query structurally couldn't answer on its own.

A subquery with `IN`: filtering against a set of values

SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

This answers "which customers have placed at least one order over 1000" — the subquery returns a list of customer_id values (every customer who placed a large order), and the outer query's IN checks each customer's id against that list. This is structurally different from the JOIN lesson's approach to a similar-sounding question: a JOIN would produce one row per matching order, potentially duplicating a customer who placed several large orders, while this subquery naturally returns each qualifying customer exactly once.

Correlated subqueries: the inner query depends on the outer row

SELECT * FROM orders o
WHERE amount > (
    SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id
);

This is a correlated subquery — notice o.customer_id inside the inner query, referencing the outer query's current row. Unlike the plain subquery earlier (computed once, reused for every row), a correlated subquery conceptually re-runs once per outer row, using that row's own customer_id each time — answering "orders above this specific customer's average," not the table-wide average. This is more expensive (potentially one subquery execution per row, though real database engines often optimize this internally) but answers a genuinely per-row question a non-correlated subquery can't.

Subqueries in FROM: a derived table

SELECT customer_id, order_count FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE order_count > 5;

A subquery can also appear in FROM, producing a derived table — a temporary, named result set (customer_totals here) that the outer query then queries again, as if it were a real table. This is the SQL way of expressing "first compute this grouped summary, then filter/query the summary itself" — notice this achieves the same result the aggregation lesson's HAVING clause could express more directly (GROUP BY customer_id HAVING COUNT(*) > 5); the derived-table version is more verbose here, but the same pattern becomes genuinely necessary once the outer query needs to do more than a HAVING clause alone can express — joining the derived table to other tables, for instance.

EXISTS: checking for presence without caring about the actual values

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id);

EXISTS checks only whether the subquery returns any rows at all — the SELECT 1 inside is a common convention specifically because the actual selected value doesn't matter, only whether a matching row exists. EXISTS can be more efficient than an equivalent IN subquery on some database engines, since the database can stop as soon as it finds one matching row rather than building a complete list first — the same "stop at the first match" idea the SQL fundamentals lesson covered for .exists()-style checks.

When a JOIN is the better choice instead

-- Subquery version: customer, but no order details
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);
 
-- JOIN version: customer AND the specific qualifying order's details together
SELECT customers.*, orders.amount FROM customers
INNER JOIN orders ON orders.customer_id = customers.id
WHERE orders.amount > 1000;

A subquery is the right tool when the question is fundamentally about one table, filtered using a value or set computed from another. A JOIN is the right tool when the actual goal is combining columns from both tables into the result — a subquery in WHERE/IN can only ever return values used for filtering, never additional columns to include in the output, which is exactly the JOIN lesson's territory instead.