Detailed explanation
A Common Table Expression (CTE) is a named query result defined using the WITH clause and available only to the statement immediately following it. Think of it as a query-local building block, not a permanently stored table or view. Depending on the database, that statement can be a SELECT or a supported data-modification statement. CTEs matter because they let you express complex SQL as readable, logically separated steps without creating database objects.
In your example, WITH top_customers AS (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id) SELECT * FROM top_customers WHERE spend > 10000; first defines the aggregation logic under the name top_customers. The outer query refers to that name like a table and filters the aggregated results. Logically, each customer has one row containing their total spending, and only customers above 10000 are returned. The same result could be written with a derived table or a GROUP BY with HAVING; the CTE primarily makes the stages easier to understand.
A statement can define multiple CTEs separated by commas, and later CTEs can reference earlier ones. A CTE can also be referenced more than once within its statement, such as when joining a result to itself. However, these references do not guarantee that the database calculates the result once and caches it. Depending on the database engine, version, and query, the optimizer may inline the CTE into the surrounding query or materialize its result internally. Therefore, a CTE is not automatically faster than a subquery; execution plans and measurements determine performance.
Recursive CTEs extend this idea by allowing the query definition to reference itself. They typically contain an anchor query that produces starting rows and a recursive member that derives additional rows, often connected using UNION ALL. This is useful for employee-manager hierarchies and category trees. Termination conditions and cycle handling are essential. Interviewers generally expect you to explain statement scope, distinguish readability from performance, and recognize that recursive syntax and restrictions vary across databases.
Key points to remember
- A CTE names a query result using WITH and exists only within one statement.
- It improves readability by separating complex logic into named steps.
- Multiple CTEs can be declared together, with later definitions referencing earlier ones.
- Repeated references do not guarantee caching, materialization, or better performance.
- Recursive CTEs support hierarchical traversal using an anchor and a recursive member.
Common mistakes candidates make
- Treating a CTE as a temporary table that remains available across multiple statements.
- Claiming that a CTE always executes once or is always faster than an equivalent subquery.
- Writing recursive logic without considering termination, cycles, or database-specific syntax.
Likely follow-up questions
How is a CTE different from a temporary table?
A CTE is scoped to one statement and cannot independently be indexed. A temporary table stores data for reuse across statements within its database-defined lifetime and can often have indexes.
When would you use a CTE instead of a subquery?
Use a CTE when naming intermediate results makes a complex query clearer or when the same logical result needs multiple references. Neither form is inherently faster, so compare execution plans when performance matters.
How does a recursive CTE stop?
It normally stops when the recursive member produces no additional rows. Use appropriate predicates and cycle handling; engine-specific recursion limits can provide a safeguard but do not replace correct termination logic.
Interview tip: Define a CTE, walk through the customer-spending example, and finish by clarifying its scope and lack of guaranteed performance benefits.