CTE vs Subquery

Think of a CTE as a named sticky note for one query, while a subquery is the same logic written inline. Interviewers love this question because the syntax is easy, but the real answer is about readability, reuse, and what the optimizer does.

Question: What is the difference between a CTE and a subquery in SQL?

Answer: A CTE, or Common Table Expression, is a named temporary result set that exists for one statement. A subquery is a query written inside another query, usually in the FROM, WHERE, or SELECT clause. Both can produce the same result, but CTEs are often easier to read and reuse.

Interview-Ready Answer: I’d say a CTE is just a named way to break a query into steps, while a subquery is the same kind of logic embedded inline. I use a CTE when I want clearer structure, repeated reuse, or recursion; I use a subquery when the logic is short and only needed once. Performance is not automatically better with either one, because many databases optimize them into the same plan, so I always check the execution plan if the query is important.

What each one really is

A CTE is a named query introduced by WITH. It acts like a one-time building block for the rest of the statement. A subquery is simply a query nested inside another query and usually has no name of its own.

How the database processes them under the hood

1. The parser reads the SQL and turns it into a tree of operations.
2. For a CTE, the database first records the CTE name and its definition for the current statement only.
3. The optimizer decides whether to inline it, meaning it substitutes the logic directly into the main query, or materialize it, meaning it computes an intermediate result and may reuse it. Materialize means storing the intermediate rows temporarily.
4. For a subquery, the optimizer often tries to flatten it into the outer query if that is safe and cheaper.
5. The final execution plan may end up looking very similar for both forms, which is why syntax alone does not determine speed.

CTE vs subquery: practical comparison

When to use each one

• Use a CTE when the logic has steps, like filtering, grouping, then ranking.
• Use a CTE when the same derived result is needed in more than one place in the same statement.
• Use a subquery when the logic is short, local, and only needed once.
• Use a recursive CTE for hierarchies like org charts, folder trees, or bill of materials.

Performance and complexity notes

There is no universal rule that a CTE is faster or slower than a subquery. The real cost depends on the plan: scans, joins, sorting, grouping, and whether the engine reuses an intermediate result. On a table with 10 million rows, repeating the same expensive aggregate three times can triple CPU and I/O if the optimizer does not remove the duplication. On the other hand, forcing a large result to materialize can write temp data to disk and also slow things down.

In PostgreSQL 12 and newer, non-recursive CTEs can often be inlined by default, and you can control this with MATERIALIZED or NOT MATERIALIZED. Other databases may handle CTEs differently, so the safe interview answer is: check the execution plan, not just the syntax.

Important edge cases

• A CTE is not a permanent table and usually not a temp table; it only lives for one statement.
• A CTE name can shadow table names inside that statement, so choose clear names.
• Some databases require the previous statement to end with a semicolon before WITH.
• An ORDER BY inside a subquery or CTE does not guarantee final order unless the outer query also orders the result.

Memory model: a CTE is a labeled box on your desk for this one sentence; a subquery is the same box hidden inside the sentence. Both can contain the same contents, but the labeled box is easier to pick up and reuse.

Real-world story

Imagine a checkout service for an e-commerce app that computes a daily high-risk customers report. The original query used the same expensive aggregation three times as subqueries: once to filter customers, once to rank them, and once to show the totals. At 50,000 orders a day it was fine, but after growth to 20 million rows, the dashboard started timing out.

The team refactored the query into a CTE so the aggregate was defined once and then reused. That made the SQL much easier to review, and in their database the optimizer produced a better plan. The outage symptom was classic: API requests backed up, the report endpoint returned 504s, and the database logs showed repeated statement timeouts during peak hours.

What went wrong: the engineers had assumed every subquery would be computed once and every CTE would always be cached. In reality, the execution plan decided the true cost. The fix was not just syntax; it was simplifying the query, checking the plan, and adding the right index on the join/filter columns.

Follow-up & Tricky Questions:

• When should I prefer a CTE over a subquery? Use a CTE when the logic is reused, when you want the query broken into readable stages, or when you need recursion. If the logic is tiny and used once, a subquery is perfectly fine.
• Does a CTE always run only once? No. Many databases inline non-recursive CTEs, so the optimizer may treat them like an expanded subquery. If you reference the same CTE multiple times, the engine may still decide how to execute it.
• Can I reference a CTE more than once in the same statement? Yes, and that is one of its biggest advantages. It lets you define the logic once and reuse the named result instead of copying the same subquery repeatedly.
• What is the main difference between a regular CTE and a recursive CTE? A regular CTE is just a named one-step result, while a recursive CTE can refer to itself to walk trees or hierarchies. That is something a simple subquery does not handle cleanly.
• Do CTEs and subqueries change the final result? Usually no, if they express the same logic. The output is determined by the relational operations, not by whether you wrote the intermediate step with WITH or inside parentheses.

• Is a CTE the same as a temporary table? No. A CTE is scoped to one statement and is not a stored object, while a temp table is a separate object with its own lifecycle.
• Are CTEs always faster because they are named? No. Naming helps humans, not automatically the engine. The execution plan decides whether the database reuses work, inlines it, or materializes it.
• Can a subquery always replace a CTE? No. Recursive CTEs are the big counterexample, and even non-recursive cases can become much harder to read or debug when deeply nested.

Common Mistakes:

• Thinking a CTE is automatically faster. Correction: performance comes from the execution plan, not the keyword.
• Using a deeply nested subquery when a CTE would be clearer. Correction: if the query has steps, name the steps.
• Assuming a CTE is a permanent or materialized table. Correction: it only exists for one statement, and the engine may inline it.
• Forgetting that ORDER BY in a nested query does not guarantee final order. Correction: order only at the outermost level unless your dialect has a special rule.

Memory Hook: CTE = labeled box, subquery = box inside the sentence. If you need to reuse or explain the box, label it. If it is a one-line thought, keep it inline.

Cheat Sheet:

• CTE means WITH name AS (...) and lasts for one statement.
• Subquery means a query inside another query.
• Both can often produce the same result and similar plans.
• CTEs improve readability and reuse; subqueries are compact for one-off logic.
• Recursive CTEs are a unique advantage of WITH.
• Always check the execution plan for real performance.

Practice Tasks:

• Rewrite a nested subquery into a CTE and compare readability.
• Take a CTE query and write the equivalent subquery version.
• Find one query in your own project where the same derived result is repeated twice, then refactor it into one named CTE.