Subqueries and WITH: A Query Inside a Query

When the filter value is itself an answer

"Which movies are rated above average?" You cannot write that WHERE clause yet, because the average is not a number you know, it is itself an answer the database has to compute. Fetching the average in app code and pasting it into a second query works, but it is two round trips and the value can go stale between them. A subquery solves this: a complete SELECT, wrapped in parentheses, sitting inside another query.

SELECT title, rating FROM movies
WHERE rating > (SELECT AVG(rating) FROM movies);

The database runs the inner query first, gets one value (this kind, returning a single value, is called a scalar subquery), and uses it exactly where a constant would go. One query, always-fresh value.

Subqueries that return a list: IN

A subquery can also return a whole column of values, and then it plugs into IN (lesson 2-2). This gives a second way to answer lesson 6-1's "who never ordered?":

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Read it inside-out: collect every customer_id that appears in orders, then keep the customers whose id is not in that list.

One real-world caution: if the subquery's list contains a NULL, NOT IN returns no rows at all, because of the NULL comparison rules from lesson 2-3 (the database cannot prove your id is unequal to an unknown). That is why many engineers default to the LEFT JOIN ... IS NULL pattern from lesson 6-1 for find-the-missing, and keep NOT IN for columns that can never be NULL.

WITH: naming a subquery

Nesting reads inside-out, and past one level that gets hard to follow. WITH (formally a common table expression, or CTE) lets you name a subquery up front and then use the name like a table, so the query reads top to bottom:

WITH totals AS (
  SELECT customer_id, SUM(price) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT customers.name, totals.total
FROM totals
JOIN customers ON customers.id = totals.customer_id;

First compute per-customer totals and call the result totals, then join it to customers for names. Same answer as lesson 5-3's join + GROUP BY, but each step has a name, and in real codebases, where queries run to dozens of lines, WITH is what keeps them readable. You can even stack several: WITH a AS (...), b AS (...) SELECT ....