Subqueries and CTEs

What is it? #

A subquery is a query inside another query. You use it when the answer you need depends on a second question — "customers who spent more than average" needs the average first.

A CTE (common table expression) is the same idea written differently. WITH name AS (...) gives a subquery a name at the top of the statement, and then you use that name below like a table.

They can express the same things. The difference is readability: a query with three nested subqueries has to be read inside-out, while the same logic as three CTEs reads top to bottom, like steps.

Then there is one thing only a CTE can do: recursion, for data that refers to itself — org charts, category trees, threaded comments.

Think of it like this #

A subquery is a sentence with a clause inside a clause inside a clause. Technically fine, genuinely hard to follow.

A CTE is the same explanation written as numbered steps. "First, work out each customer's total. Second, work out the average of those totals. Third, list the customers above it." Each step has a name, and the next step uses it.

Recursion is the step that says "and now do that again to whatever you just found, until there is nothing left" — which is how you walk down a tree of unknown depth.

Simple example #

Three questions that each need a second query inside them:

"Which customers spent more than the overall average?" "Which products have never been ordered?" "Show the full management chain above one employee." The first two are natural subqueries, the third needs recursion.

Code #

-- ---------- Scalar subquery: returns ONE value ----------

SELECT name, total
FROM orders
WHERE total > (SELECT avg(total) FROM orders);   -- the inner query returns one number

-- The subquery runs first, produces a single value, and the outer query uses it.

-- ---------- IN: match against a LIST of values ----------

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

-- "customers whose id appears in the list of customer_ids from big orders"

-- ---------- EXISTS: does at least one matching row exist? ----------

SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1                      -- the value is irrelevant; only existence matters
    FROM orders o
    WHERE o.customer_id = c.id    -- correlated: refers to the OUTER query's row
      AND o.total > 500
);

-- EXISTS stops as soon as it finds one match, so it does not build a full list.

-- ---------- NOT EXISTS vs NOT IN: an important difference ----------

-- SAFE: products never ordered
SELECT p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

-- DANGEROUS: if the inner query returns even ONE NULL, this returns NO ROWS AT ALL
SELECT p.name
FROM products p
WHERE p.id NOT IN (SELECT product_id FROM order_items);

-- Why: "id NOT IN (1, 2, NULL)" asks "is id different from NULL?",
-- and the answer is UNKNOWN, never true. So nothing is ever returned.
-- Rule: prefer NOT EXISTS. It behaves correctly with NULLs.

-- ---------- The same query, nested vs. as CTEs ----------

-- Nested: read it inside-out. Hard work.
SELECT city, spent FROM (
    SELECT city, sum(total) AS spent FROM orders WHERE status = 'completed' GROUP BY city
) t
WHERE spent > (
    SELECT avg(spent) FROM (
        SELECT sum(total) AS spent FROM orders WHERE status='completed' GROUP BY city
    ) t2
);

-- As CTEs: read it top to bottom, as three named steps.
WITH city_totals AS (
    SELECT city, sum(total) AS spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY city
),
average_city AS (
    SELECT avg(spent) AS avg_spent FROM city_totals    -- reuses the step above
)
SELECT ct.city, ct.spent
FROM city_totals ct, average_city a
WHERE ct.spent > a.avg_spent
ORDER BY ct.spent DESC;

-- ---------- A CTE can be referenced more than once ----------

WITH monthly AS (
    SELECT date_trunc('month', placed_at) AS month, sum(total) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
)
SELECT
    this.month,
    this.revenue,
    prev.revenue                                   AS previous_month,
    round(this.revenue - prev.revenue, 2)          AS change
FROM monthly this
LEFT JOIN monthly prev                             -- same CTE, joined to itself
       ON prev.month = this.month - interval '1 month'
ORDER BY this.month DESC;

-- ---------- Recursive CTE: walking a tree ----------

CREATE TABLE employees (
    id         bigint PRIMARY KEY,
    name       text NOT NULL,
    manager_id bigint REFERENCES employees(id)
);

-- Everyone in the chain ABOVE a given employee:
WITH RECURSIVE chain AS (
    -- 1. Anchor: the starting point
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE name = 'Asha Verma'

    UNION ALL

    -- 2. Recursive part: for each row found, fetch that person's manager
    SELECT e.id, e.name, e.manager_id, c.level + 1
    FROM employees e
    JOIN chain c ON e.id = c.manager_id     -- refers to the CTE being defined
)
SELECT level, name FROM chain ORDER BY level;

-- 1 | Asha Verma
-- 2 | Ravi Nair        (Asha's manager)
-- 3 | Meera Iyer       (Ravi's manager)
-- Stops automatically when the recursive part returns no more rows.

-- ---------- Recursion the other way: a whole subtree ----------

WITH RECURSIVE subtree AS (
    SELECT id, name, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL              -- start at the roots

    UNION ALL

    SELECT c.id, c.name, s.depth + 1
    FROM categories c
    JOIN subtree s ON c.parent_id = s.id -- walk DOWNWARD this time
    WHERE s.depth < 10                   -- SAFETY: stop runaway recursion
)
SELECT repeat('  ', depth - 1) || name AS tree
FROM subtree;

How it works #

A scalar subquery returns exactly one value and can be used anywhere a value is allowed. If it returns more than one row, PostgreSQL raises an error rather than picking one.

IN and EXISTS answer nearly the same question by different routes. IN builds the inner result and checks for membership. EXISTS is correlated — it references the outer row and asks only "is there at least one match?", stopping at the first one it finds. PostgreSQL's planner is good enough that the performance difference is usually small, so choose on correctness and clarity.

And on correctness, NOT IN has a real trap. If the inner query returns even a single NULL, the whole condition can never be true. id NOT IN (1, 2, NULL) requires id <> NULL, which evaluates to unknown, so the row is never kept — and the query silently returns nothing at all. This is one of the most confusing bugs in SQL precisely because there is no error message. NOT EXISTS has no such problem, which is why it is the better default.

A CTE is a named subquery defined before the main query. Since PostgreSQL 12, a CTE used once is normally inlined — folded into the main query and optimised as a whole, so there is no performance penalty for writing readable SQL. Before version 12, CTEs were always materialised into a temporary result, which is why older advice warns against them. You can still force either behaviour explicitly with MATERIALIZED or NOT MATERIALIZED when you have a reason.

A recursive CTE has two parts joined by UNION ALL. The anchor runs once and produces the starting rows. The recursive part then runs repeatedly, each time using only the rows produced by the previous round, until a round produces nothing. That termination condition is automatic — but only if the data really is a tree. A cycle in the data (A reports to B, B reports to A) would loop forever, which is why a depth guard is worth adding on anything you do not fully control.

Recursion works equally well upward (follow manager_id to the top) or downward (find everyone below), depending on which way the join in the recursive part points.

Real-world use #

CTEs are mostly a readability tool, and readability is a real production concern. A reporting query that another person has to modify at 2am is genuinely better as five named steps than as five levels of nesting. Name the steps after what they mean — active_customers, monthly_revenue — and the query documents itself.

Recursive CTEs handle the hierarchies that appear in almost every application: category trees, organisation charts, threaded comments, bill-of-materials. The alternative is fetching one level at a time from application code, which means one query per level and a round trip for each.

NOT EXISTS is the right default for "find things with no matching row" — products never ordered, users who never logged in, invoices never paid. Reach for it automatically and the NOT IN NULL trap can never bite you.

Be aware that a CTE referenced several times may be computed more than once when it is inlined. If the step is expensive and used repeatedly, WITH step AS MATERIALIZED (...) computes it once and reuses the result. Check with EXPLAIN ANALYZE rather than guessing.

When a CTE chain grows past five or six steps and runs on a schedule, that is usually the signal to turn it into a view or a materialised view, which the next lesson covers.

Common mistakes #

• Using NOT IN with a subquery that can return NULL, which silently returns no rows at all.
• Reading and writing deeply nested subqueries where named CTE steps would be far clearer.
• Writing a recursive CTE with no depth guard against data that may contain a cycle.
• Assuming CTEs are always slower — since PostgreSQL 12 a single-use CTE is normally inlined.
• Using a scalar subquery that can return more than one row, which raises an error at runtime.

Practice #

Write three queries against your shop schema. First, list customers who have spent more than the average customer, using CTEs so each step is named. Second, list products that have never been ordered — using NOT EXISTS, then try the NOT IN version and make the inner query return a NULL to see it return nothing. Third, build a small categories table with a parent_id and write a recursive CTE that prints the whole tree indented by depth.