Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
CTE vs subquery comes down to readability, reuse, and recursion. Here's when each one wins, plus the PostgreSQL optimization-fence gotcha you should know.
Quick answer
Use a CTE (WITH clause) when a query is complex, references the same result more than once, or needs recursion — it reads top-to-bottom and names each step. Use a subquery for simple, one-off, or correlated lookups where naming a separate step adds nothing. On modern engines they usually perform identically, so choose based on readability.
FAQs
Are CTEs faster than subqueries?
On modern databases they're usually the same speed because the optimizer treats them equivalently. PostgreSQL 12+, MySQL 8+, and SQL Server inline single-use CTEs into the main query. The notable exception is PostgreSQL 11 and earlier, where CTEs were always materialized and could be slower, so choose based on readability unless profiling says otherwise.
Can a CTE do something a subquery can't?
Yes — recursion. A recursive CTE (WITH RECURSIVE) can traverse hierarchical data like org charts, bill-of-materials, or category trees, which a plain subquery cannot express. CTEs can also be referenced multiple times within one query, whereas a subquery must be repeated each place it's needed.
What is the CTE optimization fence in PostgreSQL?
Before PostgreSQL 12, every CTE was materialized into a temporary result that the planner could not optimize across — it couldn't push WHERE filters into the CTE. This "optimization fence" sometimes made CTEs slower than subqueries. Postgres 12 changed the default to inline single-use CTEs; you can still force the old behavior with AS MATERIALIZED.
Should I always use CTEs instead of subqueries?
No. CTEs shine for complex, reused, or recursive logic, but a simple scalar filter or a correlated EXISTS reads more clearly as an inline subquery. Hoisting trivial logic into a WITH block adds ceremony without improving clarity. Match the construct to the complexity of the logic.
Try it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.
CTE vs subquery is mostly a readability decision, not a performance one. A common table expression (the WITH clause) names an intermediate result and lets you read a query top-to-bottom; a subquery nests that result inline where it's used. On modern databases they typically produce the same plan, so the right choice is whichever makes the query easier to understand.
There are real exceptions — recursion only works with CTEs, correlated subqueries have no clean CTE equivalent, and older PostgreSQL treated CTEs as an optimization fence. This guide covers when each wins and the one performance gotcha worth knowing.
What's the difference between a CTE and a subquery?
A subquery is a SELECT nested inside another query — in the FROM clause, WHERE clause, or SELECT list. A CTE is a named, temporary result set defined in a WITH clause at the top of the statement, then referenced by name below. They express the same logic; the difference is placement and naming.
Same logic, two shapes
-- Subquery (inline, nested)SELECT *FROM ( SELECT customer_id, SUM(total) AS lifetime FROM orders GROUP BY customer_id) tWHERE t.lifetime > 1000;-- CTE (named, top-down)WITH customer_totals AS ( SELECT customer_id, SUM(total) AS lifetime FROM orders GROUP BY customer_id)SELECT *FROM customer_totalsWHERE lifetime > 1000;
When to use a CTE
Reach for a CTE when readability or reuse is at stake:
Complex, multi-step logic — naming each step turns a nested pyramid into a readable sequence. See our guide to formatting CTEs for the layout.
The same result is used twice — define it once as a CTE and reference it in multiple places instead of copy-pasting a subquery.
Recursion — hierarchical data like org charts or category trees requires a recursive CTE (WITH RECURSIVE). A subquery simply can't do it.
Recursive CTE: only a CTE can do this
WITH RECURSIVE subordinates AS ( SELECT id, manager_id, name FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.manager_id, e.name FROM employees e JOIN subordinates s ON e.manager_id = s.id)SELECT * FROM subordinates;
When a subquery is the better choice
Subqueries win when the logic is small and local:
A simple, one-off filter — a scalar subquery in WHERE (WHERE price > (SELECT AVG(price) FROM products)) is clearer inline than hoisted into a CTE.
Correlated logic — a subquery that references the outer row (EXISTS, IN with a correlation) is awkward or impossible to express as a plain CTE.
Ad-hoc exploration — when you're poking at data and won't keep the query, a quick subquery saves the ceremony of a WITH block.
A scalar subquery reads fine inline
SELECT name, priceFROM productsWHERE price > (SELECT AVG(price) FROM products);
Do CTEs perform worse than subqueries?
Usually not — but there's history here. Before version 12, PostgreSQL always materialized CTEs: each CTE was computed once into a temporary result and the planner couldn't push filters into it, an "optimization fence" that sometimes made CTEs slower than the equivalent subquery. As the PostgreSQL WITH documentation explains, Postgres 12+ now inlines a CTE that is non-recursive, side-effect-free, and referenced only once, so it performs like a subquery.
Two practical takeaways: on Postgres 12+, MySQL 8+, and SQL Server, prefer the readable form and trust the optimizer. On Postgres 11 or older, test a hot CTE against the subquery version — and if you want the old materialize-once behavior on 12+, ask for it explicitly with AS MATERIALIZED.
Readability: the real deciding factor
Once three or four subqueries nest inside each other, you read them inside-out, and maintenance becomes painful. CTEs flatten that into a named sequence you read top-to-bottom — each step has a label that documents intent. That clarity is why analytics and dbt-style codebases lean heavily on CTEs even when a subquery would run identically.
Checklist: CTE or subquery?
Need recursion (trees, hierarchies)? Use a recursive CTE — no choice.
Reusing the same result more than once? CTE, to avoid duplicating logic.
Three or more levels of nesting? CTE, for top-down readability.
Simple scalar or correlated filter? Subquery, inline where it's used.
On PostgreSQL 11 or older and the query is hot? Benchmark both — the CTE may be fenced.
Otherwise? Pick the more readable form and let the optimizer handle it.
Wrapping up
CTE vs subquery is a readability call on modern engines: CTEs for complex, reused, or recursive logic; subqueries for simple, local, or correlated lookups. Whichever you choose, consistent formatting is what keeps it readable — sqlfmt formats CTE chains, nested subqueries, and window functions into a clean, consistent style across every dialect.
Force materialization on PostgreSQL 12+ when you want it
WITH heavy AS MATERIALIZED ( SELECT customer_id, SUM(total) AS lifetime FROM orders GROUP BY customer_id)SELECT * FROM heavy WHERE lifetime > 1000;