What is a Common Table Expression (CTE) in SQL?
Learn what a CTE is, how the WITH clause names a temporary result set, and why CTEs improve SQL readability over subqueries.
Expected Interview Answer
A Common Table Expression, or CTE, is a named, temporary result set defined with a WITH clause that exists only for the duration of the single statement that references it, letting you break a complex query into readable, sequential building blocks.
A CTE is written as WITH cte_name AS (SELECT ...) followed by a query that references cte_name as if it were a table. Unlike a subquery buried inside a FROM clause, a CTE is named up front and can be referenced multiple times in the outer query, and multiple CTEs can be chained together, each one able to build on the ones defined before it. CTEs do not persist after the statement finishes and do not create any object in the database schema, so they are purely a readability and structuring tool for a single query execution.
- Breaks complex logic into named, sequential steps
- Improves readability over deeply nested subqueries
- Can be referenced multiple times in one statement
- Supports recursion for hierarchical data
AI Mentor Explanation
Think of a CTE like a pre-match team huddle where the coach writes the starting eleven on a whiteboard before discussing the batting order, so everyone can point to "the list" repeatedly during the talk instead of re-reading the full squad sheet each time. The whiteboard list only exists for that one team meeting and is wiped afterward. A CTE works the same way: it names a temporary result set once, lets the rest of the query refer back to it by name, and disappears once the query finishes.
Step-by-Step Explanation
Step 1
Write the WITH clause
Start the statement with WITH cte_name AS ( SELECT ... ) to define the named temporary result set.
Step 2
Reference the CTE by name
In the following SELECT, JOIN, or other clause, use cte_name exactly as you would reference a regular table.
Step 3
Chain additional CTEs if needed
Separate multiple CTE definitions with commas, and let later CTEs reference earlier ones.
Step 4
Execute the full statement
The database materializes or inlines the CTE for this execution only; it is discarded once the statement completes.
What Interviewer Expects
- Clear definition of a CTE as a named, temporary result set scoped to one statement
- Comparison to subqueries in terms of readability and reuse
- Correct WITH syntax with at least one example
- Awareness that CTEs are not persisted or indexed like tables
Common Mistakes
- Claiming a CTE is always materialized and therefore always faster than a subquery
- Confusing a CTE with a permanently stored view
- Forgetting that a non-recursive CTE cannot reference itself
- Not knowing multiple CTEs can be chained in one WITH clause
Best Answer (HR Friendly)
“A CTE is a way to give a temporary name to a piece of a query using a WITH clause, so I can build up complex logic in readable, named steps instead of nesting subqueries inside each other. It only exists for that one query and disappears afterward, but while it's active I can reference it just like a regular table, even more than once.”
Code Example
WITH high_value_orders AS (
SELECT customer_id, order_id, total
FROM Orders
WHERE total > 1000
)
SELECT c.name, h.order_id, h.total
FROM high_value_orders h
JOIN Customers c ON c.customer_id = h.customer_id
ORDER BY h.total DESC;Follow-up Questions
- How is a CTE different from a subquery in the FROM clause?
- Can a CTE reference another CTE defined earlier in the same WITH clause?
- Does a CTE get indexed or does it just wrap the underlying query?
- What is a recursive CTE and when would you use one?
MCQ Practice
1. A Common Table Expression is defined using which keyword?
A CTE begins with WITH cte_name AS (query), naming a temporary result set for the statement that follows.
2. How long does a non-recursive CTE persist?
A CTE is scoped to the single statement it is defined in and is discarded once that statement finishes executing.
3. What is a key readability advantage of a CTE over a nested subquery?
Naming the intermediate result and referencing it by name reads more clearly than deeply nested, unnamed subqueries.
Flash Cards
What does CTE stand for? — Common Table Expression — a named, temporary result set defined with WITH.
How long does a CTE live? — Only for the duration of the single SQL statement that defines and uses it.
CTE vs subquery? — A CTE is named up front and can be reused or chained; a subquery is unnamed and nested inline.
Can multiple CTEs be defined together? — Yes, separate them with commas in one WITH clause, and later ones can reference earlier ones.