CTE vs Subquery: Which Performs Better?
Compare CTE and subquery performance, learn how modern optimizers inline CTEs, and how to check execution plans to decide.
Expected Interview Answer
Neither a CTE nor a subquery is inherently faster; on most modern optimizers, including PostgreSQL 12+ and SQL Server, a non-recursive CTE is inlined into the outer query and optimized exactly like an equivalent subquery, so performance differences come from optimizer-specific materialization behavior rather than from choosing one syntax over the other.
Historically, some databases always materialized a CTE's result before running the outer query, which could help by computing an expensive result once, or hurt by preventing the optimizer from pushing filters down into it. Modern optimizers instead treat a CTE the same as an inline subquery by default, folding it into the overall query plan unless it is referenced multiple times or the engine detects a reason to materialize, such as a CTE containing side effects or being explicitly hinted. The practical takeaway is to always check the actual execution plan for your specific database version rather than assuming either form is faster, and to choose based on readability first since the optimizer usually treats them equivalently.
- CTEs read top-to-bottom, improving maintainability of complex logic
- Modern optimizers inline non-recursive CTEs like subqueries by default
- Materialization can help when a CTE is reused multiple times in one query
- Execution plan inspection reveals the true cost, not the syntax choice
AI Mentor Explanation
Think of two coaches drawing up the same fielding plan, one writing it as a single flowing paragraph and the other breaking it into numbered steps on a separate card; both plans result in the exact same players standing in the exact same positions once execution begins. The numbered card is easier to read and adjust, but it doesn't make the fielders move any faster. A CTE and a subquery are the same: different notation for the optimizer, usually producing the identical underlying execution plan.
Step-by-Step Explanation
Step 1
Write the query both ways
Express the same logic as a CTE and as an equivalent inline subquery for comparison.
Step 2
Capture the execution plan for each
Use EXPLAIN or EXPLAIN ANALYZE on both versions to see how the optimizer actually handles them.
Step 3
Compare plan operators and costs
Check whether the CTE was inlined or materialized, and compare estimated versus actual row counts and cost.
Step 4
Choose based on evidence, not assumption
Pick the form with the better plan for your data and engine, defaulting to the more readable one when plans are equivalent.
What Interviewer Expects
- Correct claim that modern optimizers usually inline non-recursive CTEs like subqueries
- Awareness that older or specific engines may always materialize a CTE
- Mention of checking the actual execution plan rather than assuming
- Recognition that readability, not raw speed, is often the deciding factor
Common Mistakes
- Stating flatly that CTEs are always slower (or always faster) than subqueries
- Not knowing that materialization behavior is engine- and version-specific
- Forgetting to verify with an actual execution plan before optimizing
- Assuming a CTE referenced many times is always cheap without checking for repeated materialization cost
Best Answer (HR Friendly)
“In most modern databases, a CTE and an equivalent subquery end up running the same way under the hood, because the optimizer folds the CTE into the query plan just like it would a subquery. I would not rewrite a query from one form to the other purely for speed without first checking the actual execution plan, since the real difference usually comes down to readability rather than performance.”
Code Example
-- As a CTE
WITH recent_orders AS (
SELECT customer_id, total
FROM Orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
)
SELECT customer_id, SUM(total) AS spend
FROM recent_orders
GROUP BY customer_id;
-- As an equivalent subquery
SELECT customer_id, SUM(total) AS spend
FROM (
SELECT customer_id, total
FROM Orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
) AS recent_orders
GROUP BY customer_id;
-- Compare with: EXPLAIN ANALYZE <query>Follow-up Questions
- What conditions cause a database to force materialization of a CTE?
- How would you read an execution plan to tell if a CTE was inlined or materialized?
- When might explicitly forcing materialization of a CTE improve performance?
- Why might a CTE referenced multiple times in one query behave differently from a subquery repeated multiple times?
MCQ Practice
1. In most modern optimizers, how is a non-recursive CTE typically treated?
Modern optimizers generally fold non-recursive CTEs into the overall plan the same way they would an equivalent subquery.
2. What is the most reliable way to compare CTE vs subquery performance for a specific query?
Only the execution plan reveals whether the engine inlined or materialized the CTE and what the real cost is.
3. When can a CTE realistically outperform a repeated subquery?
If materialized, a CTE computed once and reused avoids recomputation compared to a subquery repeated at each reference.
Flash Cards
Is a CTE always faster than a subquery? — No, modern optimizers usually treat non-recursive CTEs the same as equivalent subqueries.
How do you truly compare CTE vs subquery performance? — Inspect the actual execution plan for your specific database and version.
When might a CTE help performance? — When referenced multiple times and the engine materializes it once instead of recomputing repeatedly.
What usually drives the choice between CTE and subquery? — Readability and maintainability, since performance is often equivalent.