Most of the time a CTE and a subquery compile to the same plan and the choice is pure readability. So the useful question is not "which is faster" — it's which three cases actually differ, and why the "CTEs are slower" advice you've read was true on one engine, on versions that shipped years ago.
Both of these return the five biggest orders above the average order value. Same rows, same order, same numbers. If you are picking between them for a query like this, you are picking a writing style — not a performance strategy.
SELECT customer_id, total FROM orders WHERE total > (SELECT AVG(total) FROM orders) ORDER BY total DESC LIMIT 5
WITH avg_order AS (SELECT AVG(total) AS avg_total FROM orders) SELECT o.customer_id, o.total FROM orders o, avg_order a WHERE o.total > a.avg_total ORDER BY o.total DESC LIMIT 5
| customer_id | total |
|---|---|
| 1 | 1299.99 |
| 4 | 599.99 |
| 5 | 549.99 |
The CTE version is two lines longer for no gain. That is the honest verdict on the majority of cases, and it's worth saying out loud before the three cases where it flips.
This query needs per-customer spend three times: to list it, to compute each customer's share of the whole, and to filter to above-average customers. With a CTE you name it once and refer to the name.
WITH per_customer AS ( SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id ) SELECT p.customer_id, p.spend, ROUND(p.spend * 100.0 / (SELECT SUM(spend) FROM per_customer), 1) AS pct_of_total FROM per_customer p WHERE p.spend > (SELECT AVG(spend) FROM per_customer) ORDER BY p.spend DESC
| customer_id | spend | pct_of_total |
|---|---|---|
| 1 | 2039.92 | 25.0 |
| 5 | 1209.96 | 14.8 |
| 2 | 1079.94 | 13.2 |
Write that with subqueries and the SELECT customer_id, SUM(total) … GROUP BY customer_id block appears three times. The problem is not typing — it's drift. Six months later someone adds WHERE status = 'completed' to two of the three copies, and the percentages silently stop summing to 100. A CTE makes that class of bug impossible to write.
A subquery chain has to be read from the innermost parenthesis outwards, which is the opposite of the order the work happens in. Chained CTEs read top to bottom, and every intermediate step has a name you can say out loud.
WITH monthly AS ( SELECT substr(order_date, 1, 7) AS ym, SUM(total) AS revenue FROM orders GROUP BY substr(order_date, 1, 7) ), with_prev AS ( SELECT ym, revenue, LAG(revenue) OVER (ORDER BY ym) AS prev FROM monthly ) SELECT ym, revenue, prev, CASE WHEN prev IS NULL THEN NULL ELSE ROUND((revenue - prev) * 100.0 / prev, 1) END AS pct_change FROM with_prev ORDER BY ym
| ym | revenue | prev | pct_change |
|---|---|---|---|
| 2024-01 | 4471.81 | NULL | NULL |
| 2024-02 | 3684.63 | 4471.81 | -17.6 |
There is a real constraint hiding here, not just a style one. You cannot put a window function in a WHERE clause, and you cannot nest one window function directly inside another. Any time you need to filter on or build atop a window result — rank then take the top 3, lag then compute the change — you need a second layer. A CTE is the cleanest way to get one, which is why almost every window-function interview answer has a WITH at the top.
This is the only case that is not a judgement call. Recursion needs a construct that can refer to itself, and a subquery has no name to refer to. WITH RECURSIVE is the only option in standard SQL.
WITH RECURSIVE counter(n) AS ( SELECT 1 -- anchor: where it starts UNION ALL SELECT n + 1 FROM counter WHERE n < 5 -- step + stop condition ) SELECT n FROM counter
Swap the counter for an employee table joined to its own manager_id and the same three-part shape walks an org chart of any depth. The WHERE in the recursive arm is not optional — it is the stop condition, and without it (or with a cycle in your data) the query does not terminate.
It comes down to one thing: does the engine materialize the CTE — compute it once into a temporary result — or inline it into the outer query so the optimizer can push filters down into it? Materializing blocks predicate pushdown. That is the whole mechanism.
| Engine | Default behaviour | Control |
|---|---|---|
| PostgreSQL ≤ 11 | Always materialized — CTEs were an optimization fence. This is the origin of the folklore. | None. People exploited the fence deliberately as a query hint. |
| PostgreSQL 12+ | Inlined when the CTE is not recursive and has no side effects. | MATERIALIZED / NOT MATERIALIZED to force either way. |
| MySQL 8.0+ | Optimizer chooses per query between merging and materializing. Recursive CTEs are always materialized. | Optimizer hints, since 8.0.14. |
| MySQL ≤ 5.7 | No WITH clause at all — the question doesn't arise. | Use a derived table in FROM. |
The practical upshot. Do not choose between a CTE and a subquery on performance grounds unless you have read the plan on your engine, at your version, with your data volumes. If a CTE is genuinely slow on Postgres 12+, the usual cause is the opposite of the folklore: it got inlined and re-evaluated per reference when you wanted it computed once — and the fix is to write MATERIALIZED, not to rewrite it as a subquery.
Sources: PostgreSQL feature matrix — inlined WITH queries · Crunchy Data on the PG12 change · MySQL 8.0 manual §10.2.2.4, merging vs materialization. Checked July 2026 — re-check before quoting, this is exactly the kind of claim that goes stale.
The difference is scope, not size or speed. A CTE lives for one statement. A temp table lives for the session.
Subquery
CTE
Temp table
Reach for a temp table when multiple separate queries need the same expensive intermediate result, or when the intermediate is big enough that an index on it changes the plan. Not because the CTE "felt slow" — verify that first.
The ladder below is deliberately ordered: single-value subquery, set subquery, your first WITH, then the cases where the CTE earns its keep.
A Subquery That Returns One Value
The scalar subquery — the shape from the top of this page.
A Subquery That Returns a Set (IN)
Same idea, but the subquery returns a whole column and IN checks membership.
Give a Query a Name (WITH)
Your first CTE, doing exactly what the subquery did — on purpose.
Below Department Average
A correlated subquery — the case where the subquery is the natural fit.
Employee Tenure Bands
CTE plus CASE plus GROUP BY — the "named step" pattern from case 2.
Month-over-Month Revenue Growth
The chained-CTE + LAG query above, graded against the real table.
Recursive Org Chart Traversal
Case 3 on real data. No subquery can do this one.
It's "walk me through this five-step query." Chained CTEs are how you answer that out loud. Practise writing them until the structure is automatic.
Start Free — No Signup ⚡250+ challenges · 5 sample databases · Browser only · Pro from $29/month or $199 one-time