CTE vs Subquery
Performance, Differences & When to Use Each
A CTE isn't faster than a subquery by itself: when it is referenced once, most engines fold it into the same plan as the equivalent subquery. The difference appears when a CTE is referenced more than once. PostgreSQL then computes it once, MySQL treats it like a derived table, and SQL Server runs its query again each time.
CTE vs subquery performance: is one faster?
Neither is faster by definition. In PostgreSQL 12 and later and in MySQL 8, a CTE used once and the same logic written as a subquery are folded into 1 plan, so they run the same. The performance difference only appears when a CTE is referenced more than once, and then it depends on the engine: PostgreSQL computes it once and stores it, SQL Server runs its query again for each reference.
That leaves readability and what each form is allowed to do. A CTE names a step and sits at the top of the query, and it can be reused. A subquery sits where it is used, can be correlated to the outer row, and is the only form that fits in a WHERE predicate.
Know CTE vs subquery the way the interviewer who asks it knows it.
When a CTE is inlined and when it is materialized
Inlining means the optimizer treats the CTE's text as if you had written it in place, so a filter on the outer query can move down to the base table and use its indexes. Materializing means the CTE is computed first into a temporary result, and the outer query reads that result as it is. The PostgreSQL manual shows the contrast with 1 table: a CTE over big_table filtered by key = 123 is folded into the outer query and can use an index on key.
Reference the same CTE twice in a self join and PostgreSQL materializes it. The manual describes a temporary copy of big_table that is joined with itself without the benefit of any index, because the optimizer cannot push a restriction into a query that more than 1 reference uses. Adding NOT MATERIALIZED lets the filter reach the table, at the risk of computing the CTE twice. Adding MATERIALIZED does the opposite, and it is the right choice when the CTE is expensive, for instance when it calls a costly function that should run once per row.
How PostgreSQL, MySQL and SQL Server treat a CTE
PostgreSQL changed its behaviour in version 12. Before it, a CTE was always evaluated before the rest of the query, so it acted as an optimization fence. Since then a non-recursive CTE with no side effects that is referenced once is inlined automatically, which means old advice to avoid CTEs for speed no longer holds there. A CTE referenced more than once is evaluated once per execution of the parent query and its result is reused.
MySQL handles derived tables and views the same way as CTEs, and avoids materialization whenever it can. A subquery or CTE containing an aggregate or window function can't be merged, and neither can one containing DISTINCT, GROUP BY, HAVING, LIMIT or UNION, so it's materialized. So is any merge that would reference more than 61 base tables. A materialized CTE is built once per query however many times it is referenced, and a recursive CTE is always materialized. The details are in the MySQL 8.4 manual's derived table optimization page.
SQL Server is the outlier. Its documentation states that CTE results are not materialized and that each outer reference requires the defined query to be re-executed. A CTE referenced 3 times can therefore cost 3 runs, and the documentation recommends a temporary table when a result is needed more than once.
CTE vs subquery: what each form can do
- Named step
- Reuse in one query
- Recursion
- Correlated to outer row
- Reads top to bottom
- Use inside WHERE
- Named stepYes
- Reuse in one queryMany references
- RecursionYes
- Correlated to outer rowNo
- Reads top to bottomYes
- Use inside WHEREOnly via a subquery
- Named stepOnly as a derived table
- Reuse in one queryRepeat the text
- RecursionNo
- Correlated to outer rowYes
- Reads top to bottomInside out
- Use inside WHEREYes
When to use a subquery and when to use a CTE
Choose a subquery for a check that is short and used once. WHERE id IN (SELECT ...) and WHERE EXISTS (SELECT 1 ...) checks and a scalar value such as (SELECT MAX(ts) FROM events) read best in place, and the correlated form, where the inner query uses a column of the outer row, has no CTE equivalent. A CTE can't see the outer query's rows. The SQL subquery guide covers the scalar and row types as well as table and correlated subqueries.
Choose a CTE when the query has 3 or more steps, when each step deserves a name such as daily_orders or first_purchase, or when the same intermediate result feeds 2 branches. Nested subqueries read inside out and get hard to review; the same logic as a chain of CTEs reads top to bottom and lets you test each step by selecting from it. The SQL CTE guide walks through the syntax.
The strongest performance reason to prefer a CTE is an expensive step that you really need once, on an engine that materializes it. The strongest reason to avoid one is the reverse: a large CTE referenced by several branches on an engine that re-runs it, or one that gets copied into a temporary result before a selective filter could have cut it down.
The same query as a CTE, a derived table and a hinted CTE
/* CTE form */
WITH big_spenders AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT c.name, b.total
FROM customers c
JOIN big_spenders b ON b.customer_id = c.id
WHERE b.total > 1000;
/* Derived table (subquery in FROM) form */
SELECT c.name, b.total
FROM customers c
JOIN (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) b ON b.customer_id = c.id
WHERE b.total > 1000;
/* PostgreSQL: choose the plan yourself */
WITH big_spenders AS MATERIALIZED (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT c.name, b.total
FROM customers c
JOIN big_spenders b ON b.customer_id = c.id;The first 2 forms return the same rows and usually share a plan; the third pins PostgreSQL to materialization
How to check which one your database ran
Run the query under EXPLAIN, and under EXPLAIN ANALYZE in PostgreSQL where running it is safe, then read the plan instead of guessing. An inlined CTE leaves no trace of its name: the filter appears on the base table's scan and an index scan is possible. A materialized CTE shows up as its own node, such as a CTE Scan in PostgreSQL, read once for each reference.
Compare the row counts on the scan nodes. If a CTE scan produces millions of rows that the outer query then filters to a few hundred, the filter arrived too late: try NOT MATERIALIZED in PostgreSQL, move the filter inside the CTE by hand, or rewrite the CTE as a subquery. If a CTE that is expensive to compute appears twice in the plan on SQL Server, write it to a temporary table once. Time both versions on production-sized data, since the choice between them can flip as a table grows.
Recursion is the one thing only a CTE can do
A recursive CTE reads its own output, so it can walk an org chart, expand a bill of materials or generate a date series, none of which a subquery can express. The PostgreSQL manual says RECURSIVE turns WITH from a syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. Add a guard: SQL Server stops a runaway recursion at 100 levels by default, with MAXRECURSION to change it. See recursive CTE in SQL for hierarchies and date series.
How CTE vs subquery comes up in a data engineer interview
Interviewers rarely ask for a definition. They give a multi-step problem, such as the top order per customer or a retention cohort, and watch whether you structure it as named steps, and whether you can say what the engine will do with it. A strong answer names the form you chose and why, and says what changes if a step is used twice.
Practice by solving the same problem in both forms and comparing plans on the engine you work with. The SQL practice problems give you data to do that on, and the SQL interview questions show how these questions are phrased. For the round itself, see the SQL round guide, and for harder material the advanced SQL interview questions.
CTE vs subquery FAQ
Is a CTE faster than a subquery?+
Is a CTE materialized?+
Does a CTE improve performance in SQL Server?+
When should I use a subquery instead of a CTE?+
Can a CTE be correlated like a subquery?+
Can a subquery be recursive?+
How do I check whether my CTE was inlined?+
The candidate who gets the offer
- 01
Reading a solution is not the same as writing one
Every engineer who has frozen on a query they had read a dozen times knows the gap. The only preparation that closes it is producing the answer yourself, under time, before the interview does it for you
- 02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
- 03
5 problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition
Related guides
SQL interview questions
The SQL questions data engineers are asked in real interviews
SQL practice problems
Problems to solve with CTEs and subqueries, plus joins
The SQL round
What the SQL round asks and how to prepare for it
SQL CTE guide
Syntax and examples for the WITH clause
SQL subquery guide
Scalar and row subqueries, table subqueries and correlated subqueries
Recursive CTE
Hierarchies and date series with a recursive WITH
The data engineering challenge
Dirty, production-shaped data, scored blind each week.