SQL CTEs: the WITH clause
A SQL CTE (Common Table Expression) is a named temporary result set defined with the WITH clause and referenced later in the same query. CTEs make complex queries readable by breaking them into named steps, and can be referenced multiple times or made recursive.
Why use a CTE
CTEs replace nested subqueries with named, top-to-bottom steps that read like a pipeline. You can chain several: WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b.
CTE vs subquery
Functionally similar, but a CTE is named and can be referenced more than once, which improves readability and avoids repeating a subquery.
What a CTE is and when it beats a subquery
A common table expression is a named result set defined with WITH that exists for the duration of a single statement. Functionally it does the same job as a derived table, but it is defined before it is used, so a query reads top to bottom instead of inside out. That matters most when you have several stages: you can name each step (filtered_orders, monthly_totals, ranked) and the logic becomes reviewable. You can define multiple CTEs separated by commas, and a later CTE can reference an earlier one.
Recursion, reuse and the materialisation question
WITH RECURSIVE lets a CTE reference itself, which is how you walk hierarchies such as an employee-manager chain or a bill of materials: an anchor query seeds the result and a recursive part repeatedly joins back until no new rows appear. Always give recursion a termination condition or a depth guard. One thing a CTE does not guarantee is that its result is computed once — many engines inline a CTE into each place it is referenced, so a CTE used three times may run three times. If a stage is expensive and reused, materialise it into a temporary or intermediate table rather than assuming the optimiser will cache it.
Example (SQL)
WITH region_totals AS (
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region
)
SELECT region, total_sales
FROM region_totals
WHERE total_sales > 1000
ORDER BY total_sales DESC;Defines region_totals as a CTE, then filters and sorts it — clearer than a nested subquery.
Frequently asked questions
What is a CTE in SQL?
A Common Table Expression: a named temporary result set defined with WITH and used within the same query to make it more readable.
What is the difference between a CTE and a subquery?
Both produce a temporary result, but a CTE is named and can be referenced multiple times and made recursive, improving readability.
What is a recursive CTE?
A CTE that references itself (WITH RECURSIVE ...), used for hierarchical or sequential data such as org charts or generating date series.
Practice challenges
Open the free PySpark compiler · Data Engineering challenges · Data Engineering jobs