Part 3 of 5 · 1 min read
Why a CTE often reads better than nesting
The same computation, compared side by side as nested subqueries and as a CTE.
Think of a recipe written as one long sentence with clauses nested inside clauses. Compare that to the same recipe written as numbered steps, each one building on the step before it.
In plain words: a deeply nested subquery forces a reader to parse from the innermost parentheses outward. A CTE reads top to bottom, each step named and already finished before the next step uses it. The two forms compute exactly the same result; a CTE changes only how the computation is expressed, not what it returns.
A worked example. The order_counts example above, written instead as nested subqueries, forces reading from the deepest parentheses outward to understand what AVG is even averaging.
The key lines. The same query as a single nested expression, shown to make the contrast concrete, not because it is the better version:
SELECT customer_id, order_count FROM (
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id
) oc
WHERE order_count > (
SELECT AVG(order_count) FROM (
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id
) oc2
);
Remember this
The nested version above also repeats the inner subquery's logic twice, once for each reference. The CTE version writes it once. A query optimizer may still compute it twice internally, but the source text itself only needs to say it once.