Subqueries Explained

A subquery is a SELECT nested inside another SQL statement. It runs first (conceptually), and its result is used by the outer query — as a value to compare against, a list to check membership in, or an entire table to select from.

Subquery in WHERE, with IN

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

The inner query returns a list of customer_ids with at least one order over 100; the outer query returns the names of customers whose id appears in that list. This reads naturally, but for large result sets, an equivalent JOIN is often more efficient — which one performs better depends on the database's query planner, and is worth checking with EXPLAIN (mentioned in the indexes lesson) on a real dataset rather than assuming.

Scalar subqueries: a single value

A subquery that returns exactly one row and one column can be used anywhere a single value is expected:

SELECT name, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

This finds every order priced above the average order total — the inner query computes one number, and the outer query compares each row against it.

EXISTS: checking for at least one matching row

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

EXISTS returns true as soon as the subquery finds at least one matching row, without caring what columns it actually returns — SELECT 1 is a common convention signaling "we only care whether this matches, not what it returns." This is a correlated subquery: notice o.customer_id = c.id references c, a table from the outer query. Unlike the earlier examples, this inner query can't run once on its own — it re-runs conceptually for every row the outer query considers, using that row's value each time.

Subqueries in FROM: derived tables

A subquery can also stand in for an entire table, when you need to query the result of another query:

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

This is necessary here specifically because you can't filter on an aggregate result (order_count) using WHERE directly in the same query that computes it — WHERE runs before grouping happens, a distinction the next couple of lessons cover in detail. Wrapping the aggregation in a subquery lets the outer query filter on its result as if it were an ordinary table. (The HAVING clause, covered shortly, solves this same specific problem more directly — this pattern is more useful once the filtering needs to happen after other joins or transformations too complex for a single HAVING clause.)

With ways to nest queries covered, the next lessons turn to aggregation — GROUP BY and functions like COUNT and SUM that summarize many rows into one.