Subqueries & CTEs

What is it?

Some questions can't be answered with one flat query. "Find users who have placed more than 3 orders" needs an intermediate step: first figure out which user ids have more than 3 orders, then look up those users.

A subquery is a query nested inside another one, usable in a WHERE, FROM, or SELECT clause. A CTE (Common Table Expression), written with WITH, does something similar but gives that intermediate step a name you can reference afterward like a temporary table — often making a multi-step query much easier to read.

Explain like I'm 10

Writing a subquery is like doing scratch work on the side of the page before answering the real question. A CTE is doing that same scratch work but labeling it clearly — 'Step 1: frequent buyers' — so anyone reading it later (including future you) doesn't have to mentally unpack a nested block to see what it's for.

Examples

A subquery in WHERE

SELECT name FROM users
WHERE id IN (
  SELECT user_id FROM orders
  GROUP BY user_id
  HAVING COUNT(*) > 3
);

The inner query first finds every user_id with more than 3 orders; the outer query then finds the actual users matching those ids.

The same query, using a CTE

WITH frequent_buyers AS (
  SELECT user_id FROM orders
  GROUP BY user_id
  HAVING COUNT(*) > 3
)
SELECT users.name
FROM users
JOIN frequent_buyers ON frequent_buyers.user_id = users.id;

The WITH clause names the intermediate result frequent_buyers, and the rest of the query can join against it just like a real table — arguably easier to follow than a nested subquery once there's more than one step.

How it works

Conceptually, the database evaluates the inner subquery (or each CTE) first, producing an intermediate result set, and then runs the outer query against that result. Postgres treats a CTE largely as a named, temporary result you can select from or join against; for genuinely hierarchical data (like a tree of comment replies), it also supports WITH RECURSIVE, which repeats a query against its own previous results until nothing new is found.

Why does it exist?

Real questions — "customers who bought X but never Y," "the best-selling product in each category" — often can't be expressed as a single flat SELECT. Subqueries and CTEs exist to let you build up an answer in stages within one query, without needing separate round trips to the database or manually managed temporary tables.

When to use it

Reach for a CTE when a query has multiple logical steps and naming each one makes the whole thing easier to read. Reach for an inline subquery for a small, self-contained filtering condition that doesn't need its own name.

When not to use it

If a plain JOIN or a simple aggregation already answers the question directly, wrapping it in a subquery or CTE just adds indirection. Deeply nested subqueries, several levels deep, also tend to become hard to read and to optimize — that's usually a sign to pull one level out into a named CTE, or reconsider the query's shape entirely.

Common mistakes

• Writing a correlated subquery (one that references a column from the outer query) that effectively reruns once per outer row, becoming very slow on large tables.

• Assuming a CTE is always computed once and cached — that behavior can differ across databases and versions, so it isn't guaranteed to be a free optimization.

• Nesting several layers of subqueries instead of naming intermediate steps with CTEs, making the query difficult for anyone else (or future you) to follow.

Practice exercises

1. Easy:

   Rewrite a subquery used with = into an equivalent using IN, for finding orders placed by a specific customer looked up by email.

2. Medium:

   Write a CTE that computes the average order total, then a query that selects all orders above that average.

3. Hard:

   Explain the difference between a correlated and a non-correlated subquery, with a concrete example of each and why the correlated one is typically slower.

Interview questions

What is a CTE and why use one instead of a plain subquery?

A WITH clause that names an intermediate query result — mainly used to make a multi-step query more readable by labeling each stage instead of nesting subqueries.

What is a correlated subquery?

A subquery that references a column from its outer query, meaning it conceptually needs to be re-evaluated once per row of the outer query.

Can a single query define more than one CTE?

Yes — multiple CTEs can be chained in one WITH clause, comma-separated, and later ones can reference earlier ones.