What is a CTE?
Learn what a CTE (Common Table Expression) is in SQL, how WITH clauses work, recursive CTEs for hierarchical data, and when they beat subqueries.
Expected Interview Answer
A CTE, or Common Table Expression, is a named temporary result set defined with a WITH clause that exists only for the duration of the single query it's attached to, letting you break a complex query into readable, sequential, named steps.
Unlike a subquery buried inline, a CTE is defined once at the top of the statement with WITH cte_name AS (SELECT ...) and can then be referenced by name in the main query, including multiple times if needed. CTEs improve readability for multi-step logic and, when chained, let each step build on the previous one, similar to a pipeline. A recursive CTE, declared with WITH RECURSIVE (or just RECURSIVE in some dialects), can reference itself to walk hierarchical or graph-like data such as an organization chart or a bill of materials, iterating until no new rows are produced. Most optimizers treat a non-recursive CTE like an inline view or subquery for execution purposes, meaning it does not automatically materialize or cache results in every database, so it doesn't guarantee a performance win by itself, its main benefit is clarity.
- Breaks complex, multi-step logic into readable, named stages
- Allows a result set to be referenced multiple times without repeating the query
- Recursive CTEs handle hierarchical data like org charts or category trees
- Improves maintainability compared to deeply nested subqueries
AI Mentor Explanation
A CTE is like a commentator naming a temporary stat, 'this over's run rate', calculating it once at the top of the broadcast and then referring back to that name repeatedly instead of recalculating it every time. A recursive CTE is like tracking a batting partnership build up over by over, each step adding to the previous one until the partnership ends.
How a CTE names a result set for reuse in the main query
WITH regional_totals AS (...)
- Computed once
- Named result set
Main SELECT ... FROM regional_totals
- References the CTE by name
- Can reference it multiple times
Step-by-Step Explanation
Step 1
Identify the reusable step
Find a piece of logic, like an aggregation, that the main query needs to reference, possibly more than once.
Step 2
Write the WITH clause
Define WITH cte_name AS (SELECT ...) at the top of the statement.
Step 3
Reference it in the main query
Use cte_name like a regular table in the SELECT/FROM/JOIN that follows.
Step 4
Chain multiple CTEs if needed
Separate additional CTEs with commas, letting later CTEs reference earlier ones.
Step 5
Add RECURSIVE for hierarchies
Use WITH RECURSIVE with an anchor member and a recursive member that references the CTE itself, joined by UNION ALL.
Step 6
Verify the plan and termination
Check EXPLAIN for performance and ensure the recursive member has a condition that eventually stops producing new rows.
What Interviewer Expects
- Defines a CTE as a named, temporary result set created with WITH
- Explains that a CTE only exists for the duration of its query
- Knows how a recursive CTE differs from a normal one and when to use it
- Understands a CTE mainly improves readability, not guaranteed performance
- Can compare a CTE to a subquery and explain when each is preferable
Common Mistakes
- Assuming a CTE is always materialized and cached, guaranteeing a speed-up
- Forgetting the anchor and recursive parts must be combined with UNION ALL in a recursive CTE
- Not adding a stopping condition to a recursive CTE, causing infinite recursion
- Overusing deeply chained CTEs where a simpler JOIN would be clearer
Best Answer (HR Friendly)
“A CTE is a way to give a temporary name to a chunk of a query so you can reuse it later in the same query, which makes complicated SQL much easier to read and build step by step. It's especially useful for working with hierarchical data, like an org chart, where each level depends on the one above it.”
Code Example
-- orders: id, region, amount
-- (1,'North',500), (2,'North',300), (3,'South',100)
WITH regional_totals AS (
SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region
)
SELECT region, total
FROM regional_totals
WHERE total > 400;
-- Result: North | 800-- employees: id, name, manager_id
-- (1,'CEO',NULL), (2,'VP',1), (3,'Manager',2)
WITH RECURSIVE org_chain AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL -- anchor: the CEO
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chain oc ON e.manager_id = oc.id -- recursive step
)
SELECT * FROM org_chain ORDER BY level;
-- CEO (level 1), VP (level 2), Manager (level 3)Follow-up Questions
- How does a recursive CTE avoid infinite loops?
- What is the difference between a CTE and a temporary table?
- When does a CTE actually improve query performance versus just readability?
- Can you reference one CTE from within another CTE?
- How would you use a CTE to find duplicate rows before deleting them?
MCQ Practice
1. How is a CTE defined in SQL?
A CTE is defined with a WITH clause, giving a name to a result set that the main query can then reference.
2. What keyword combination is required to make a CTE recursive in most dialects?
A recursive CTE is declared using WITH RECURSIVE, combining an anchor query and a recursive query with UNION ALL.
3. What is the main benefit a CTE provides over a deeply nested subquery?
A CTE's primary benefit is readability — breaking complex logic into named, sequential steps — not a guaranteed performance improvement.
Flash Cards
What is a CTE? — A Common Table Expression — a named, temporary result set defined with WITH that exists only for the duration of its query.
What is a recursive CTE used for? — Walking hierarchical or graph-like data, such as an org chart, by referencing the CTE within its own definition.
Does a CTE always improve performance? — Not necessarily — most optimizers treat it like an inline view or subquery; its main benefit is readability.
What two parts make up a recursive CTE? — An anchor member (base case) and a recursive member (references the CTE itself), combined with UNION ALL.