What are Common Table Expressions (CTEs) in SQL?
Learn what SQL CTEs are, how the WITH clause works, when to use recursive CTEs, and how they make complex queries readable, with clear examples.
Expected Interview Answer
A Common Table Expression (CTE) is a named, temporary result set defined with the WITH keyword that exists only for the duration of a single query and can be referenced like a table within that query.
CTEs make complex queries readable by breaking them into named building blocks instead of deeply nested subqueries. A query can define several CTEs separated by commas, and later CTEs can reference earlier ones. A recursive CTE references itself to walk hierarchies such as org charts or bill-of-materials trees, using an anchor member and a recursive member joined by UNION ALL.
- Improves readability of complex queries
- Lets you reuse a subquery result multiple times in one statement
- Enables recursion for hierarchical data
- Avoids repeating nested subqueries
- Easier to debug step by step
AI Mentor Explanation
Think of a CTE like writing the current batting partnership onto the dressing-room whiteboard before the innings analysis. You name it 'CurrentPartnership', jot the runs and balls once, and every analyst refers to that named board instead of recounting the scorecard each time. When the innings ends the board is wiped clean, exactly like a CTE that lives only for the one query that defined it.
Step-by-Step Explanation
Step 1
Start with WITH
Begin the statement with WITH followed by the CTE name and an AS clause containing the defining query in parentheses.
Step 2
Define the result set
Write the SELECT inside the parentheses that produces the temporary named rows.
Step 3
Reference it in the main query
Use the CTE name in the FROM or JOIN of the outer query as if it were a real table.
Step 4
Chain multiple CTEs
Separate additional CTEs with commas; later CTEs may reference earlier ones for layered logic.
Step 5
Add recursion when needed
For hierarchies, use WITH RECURSIVE with an anchor member and a recursive member combined by UNION ALL.
What Interviewer Expects
- Correct use of the WITH keyword
- Understanding that a CTE is scoped to one statement
- Difference between a CTE and a subquery or temp table
- Knowledge of recursive CTEs for hierarchical data
- Ability to chain multiple CTEs
Common Mistakes
- Thinking a CTE is stored or persists beyond the query
- Confusing a CTE with a materialized view or temp table
- Forgetting UNION ALL and a termination condition in recursive CTEs
- Assuming CTEs always improve performance rather than readability
- Referencing a CTE outside the single statement that defines it
Best Answer (HR Friendly)
“A CTE is a way to name a temporary chunk of a query so the overall statement is easier to read and organize. You define it once with the WITH keyword and then reuse that name in the main query, and it disappears as soon as the query finishes.”
Code Example
WITH high_earners AS (
SELECT employee_id, name, salary
FROM employees
WHERE salary > 100000
)
SELECT name, salary
FROM high_earners
ORDER BY salary DESC;WITH RECURSIVE org_chart AS (
SELECT employee_id, manager_id, name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.name, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT name, level
FROM org_chart
ORDER BY level;Follow-up Questions
- How does a CTE differ from a temporary table?
- When would you use a recursive CTE?
- Do CTEs improve query performance?
- Can you reference one CTE inside another?
- What is the difference between a CTE and a view?
MCQ Practice
1. Which keyword introduces a CTE in SQL?
A CTE is defined using the WITH keyword followed by a name and the defining query.
2. How long does a CTE's result set exist?
A CTE is scoped to the single statement in which it is declared and does not persist afterward.
3. What is required to combine the anchor and recursive members of a recursive CTE?
Recursive CTEs join the anchor member to the recursive member using UNION ALL.
Flash Cards
What keyword defines a CTE? — WITH, followed by the CTE name, AS, and the query in parentheses.
How long does a CTE live? — Only for the duration of the single statement that defines it.
What makes a CTE recursive? — An anchor member and a recursive member that references the CTE itself, combined with UNION ALL.
CTE vs subquery? — A CTE is a named, reusable block that improves readability; a subquery is inline and unnamed.