Subqueries vs CTEs: a query inside a query, made readable
Both let you use the result of one query inside another. A CTE just gives that inner query a name up front, so the whole thing reads top to bottom instead of inside-out.
Sometimes you need the result of one query as the input to another: "first work out each customer's
total spend, then keep the big spenders." You can do that two ways: a subquery (a query nested
inside another) or a CTE (the same idea, but named up front with WITH). They usually run
identically; the difference is readability.
🎯 Explain Like I'm Hired A subquery is a query tucked inside another query, in parentheses. A CTE (short for Common Table Expression, written
WITH name AS (...)) is the exact same thing, except you give it a name at the top, so the rest of the query reads like clearly-labeled steps instead of a pile of nested brackets. Example: asked to find "customers who spend more than the average customer," a CTE lets you work out the average once, name it, and refer to it by name, much easier to follow than nesting the same subquery twice.
-- Subquery: the inner query sits in parentheses, unnamed
SELECT customer_id, total_spent
FROM (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders GROUP BY customer_id
) AS spend
WHERE total_spent > 1000;
-- CTE: same logic, named "spend" up top, reads top-to-bottom
WITH spend AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM spend
WHERE total_spent > 1000;
They usually perform the same
A common myth is that CTEs are slower. On modern Postgres they're generally treated the same as subqueries, so pick whichever reads more clearly, which, for anything with more than one step, is almost always the CTE.
The one thing only a CTE can do: recursion
A CTE can refer to itself, which lets you walk a hierarchy: an org chart, a category tree, a
folder-inside-folder structure. A plain subquery can't. You'll rarely need this early on, but it's
worth knowing "if the data is tree-shaped, reach for a recursive CTE" (WITH RECURSIVE).
The interview tip
For a quick one-line filter you'll use once, a subquery is fine and less typing:
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'IN');
For anything with multiple steps, name them with CTEs. The person reading it later (often you) will thank you.