Queries inside queries, named for reading

A query where a value goes

A subquery in brackets is a whole SELECT used as a value: WHERE total > (SELECT avg(total) FROM orders) compares every row against the average computed by the inner query. Wherever a single value is expected, a query that returns one row and one column can stand — the parenthesis is the boundary between the two.

IN (SELECT ...) tests membership against a set, and EXISTS (SELECT 1 FROM ... WHERE ...) asks whether anything matches at all. The two overlap; EXISTS wins when the inner query is about the connection between tables, because it can check for a match and stop, without building the whole set first.

WITH: a name for a step

A common table expression names a subquery and puts it at the top: WITH monthly AS (SELECT ...) SELECT ... FROM monthly. Nothing is stored — the name exists for the duration of the statement — and the value is entirely in the reading: the query becomes a list of steps with names, instead of brackets inside brackets.

CTEs chain. A second one may use the first, so a report becomes "revenue per month, then the average of those, then the months above it". PostgreSQL lets a CTE be written once and referenced several times, which is how one expensive step feeds two branches of the same answer.

Choosing between them

An inner SELECT used as a value is best when the answer is genuinely a single number. IN is at its best with a short, honest list from the data. EXISTS is for a test of existence. And a CTE is what a query becomes when it is too long for brackets to stay readable — which, in practice, means most reports that survive past their first week.

One honest note: the planner is free to rewrite a subquery into a join or the other way round, and it usually does. The differences between these forms are mostly differences for the reader, and that is a real consideration — the next person to open the query is part of the program.

A subquery can see the row outside it

An uncorrelated subquery stands alone: (SELECT avg(total) FROM orders) is computed once, and its value is used wherever it appears. A correlated subquery mentions a column from the outer query, so it is logically evaluated for each outer row — EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) asks a fresh question for every customer.

The difference is not just style. A correlated EXISTS can stop at the first matching row, which makes it cheap for existence tests on indexed columns. A scalar subquery in a value position must return exactly one row and one column; two rows is an error, and zero rows is NULL, which is a quiet answer that may deserve a COALESCE.

A CTE is a name, not a saved table

A common table expression lives for one statement. PostgreSQL may inline a non-recursive CTE that is referenced once, folding it into the surrounding query, or it may materialise it when that is cheaper. The name is a promise about how the query reads, not a promise that the work happens once.

If a step is expensive and used twice, WITH name AS MATERIALIZED (...) makes the choice explicit. If the step refers to itself, WITH RECURSIVE turns it into a loop — the standard way to walk a hierarchy such as categories or an org chart. In both cases the name is still local to the statement; nothing is left behind for the next query.

Try these next

• Rewrite the IN membership test as EXISTS and compare the two plans.
• Add MATERIALIZED to a CTE used twice and see whether the plan changes.
• Write a scalar subquery that returns two rows and read the error it produces.

Subqueries and named steps

-- Every order that beat the average.
SELECT id, total
FROM orders
WHERE total > (SELECT avg(total) FROM orders)
ORDER BY total DESC;

-- Membership: customers who have ordered at all.
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- Existence: the same question, asked as a test.
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Named steps, read top to bottom.
WITH per_customer AS (
    SELECT c.name,
           count(o.id) AS orders,
           coalesce(sum(o.total), 0) AS spent
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name
)
SELECT name, orders, spent
FROM per_customer
WHERE spent > (SELECT avg(spent) FROM per_customer)
ORDER BY spent DESC;