Subqueries

A subquery is a SELECT nested inside another query. It lets one query use another query's answer — as a single value, as a list, or as a temporary table-shaped result — without leaving SQL or copying numbers by hand.

You already know every clause inside the examples below. The new move is placement: a query can sit where a value, list, or table would normally sit.

Scalar subqueries: a query as a value

A scalar subquery sits where a single value belongs, so it must return one column and no more than one row. Suppose you want orders above the café's average order amount. You could run avg(amount), memorize the number, and paste it into a second query. Better: let the second query ask the first one directly.

SELECT o.id,
       o.product,
       o.amount,
       (
         SELECT round(avg(o2.amount), 2)
         FROM orders AS o2
       ) AS avg_order_amount
FROM orders AS o
WHERE o.amount > (
  SELECT avg(o3.amount)
  FROM orders AS o3
)
ORDER BY o.amount DESC, o.id;

 id  |     product      | amount | avg_order_amount
-----+------------------+--------+------------------
 101 | Espresso Machine | 220.00 |            89.38
 104 | Espresso Machine | 220.00 |            89.38
 102 | Grinder          |  92.00 |            89.38
(3 rows)

Read the inner query first: SELECT avg(o3.amount) FROM orders AS o3 produces one value. The outer WHERE then compares each order against that value. The separate rounded copy in the SELECT list is there so the threshold is visible in the grid, not hidden in prose.

That one-value shape matters. If a scalar subquery returns two rows, the database cannot decide which value you meant. The fix is not magic punctuation — it is changing the inner query so it truly returns one value, often with an aggregate like avg, max, or sum.

IN subqueries: a query as a list

IN already accepted a literal list in Section 2: WHERE city IN ('Berlin', 'Lisbon'). A subquery lets the data build that list. Find customers whose id appears among orders of at least 100:

SELECT c.name, c.city
FROM customers AS c
WHERE c.id IN (
  SELECT o.customer_id
  FROM orders AS o
  WHERE o.amount >= 100
)
ORDER BY c.name;

 name  |  city
-------+--------
 Ada   | Berlin
 Linus | Berlin
(2 rows)

The inner query returns one column: customer ids from qualifying orders. The outer query keeps customers whose c.id is a member of that result. Duplicates in the inner result do not change the answer; membership is a yes-or-no question.

Keep the shape straight: an IN subquery must return exactly one column, because the outer expression is checking one value at a time. If you need to compare multiple facts, you are in more advanced territory. For now, make the inner query's select list match the one value on the left of IN.

Subqueries in FROM: derived tables

Sometimes the inner query returns a whole table-shaped result. Put it in FROM, give it an alias, and the outer query can treat it like a table for one statement.

Here the inner query summarizes orders to one row per customer. The outer query joins customer names onto that summary and filters to totals of at least 100:

SELECT c.name,
       totals.order_count,
       totals.total_amount
FROM (
  SELECT o.customer_id,
         count(*) AS order_count,
         sum(o.amount) AS total_amount
  FROM orders AS o
  WHERE o.customer_id IS NOT NULL
  GROUP BY o.customer_id
) AS totals
JOIN customers AS c
  ON c.id = totals.customer_id
WHERE totals.total_amount >= 100
ORDER BY totals.total_amount DESC, c.name;

  name  | order_count | total_amount
--------+-------------+--------------
 Ada    |           2 |       312.00
 Linus  |           2 |       228.00
 Edsger |           2 |       105.00
(3 rows)

This is called a derived table: a table derived from a query. It is not stored, and it does not change the database. It exists only while this statement runs.

The alias is not decoration. AS totals names the inner result so the outer query can say totals.order_count and totals.total_amount. In multi-table queries, keep qualifying columns the way joins taught you; once a derived table joins another table, bare column names become a guessing game for the reader even when the parser accepts them.

Where subqueries shine — and where they turn into nesting soup

Subqueries shine when the sentence naturally has two layers:

• "Rows above the average" — compute the average inside the filter.
• "Customers whose ids appear in qualifying orders" — build the list from live data.
• "Filter a grouped result" — make the grouped result first, then query it one step later.

They get messy when each layer hides another layer. Three nested SELECTs, repeated copies of the same inner query, or a derived table with ten calculated columns is usually a sign that the query wants a more readable shape. The answer is not to avoid subqueries; it is to keep each one small enough that you can name what it returns in one sentence.

Use this playground to try the three shapes side by side:

Next lesson adds the twist this one deliberately avoided: the inner query will be able to see the current row from the outer query. That unlocks correlated subqueries and the EXISTS pattern.