Introduction
A Common Table Expression (CTE), defined with the WITH keyword, creates a named, temporary result set that exists only for the duration of the statement that follows it. CTEs make complex queries more readable by breaking them into logical, named steps, and they can reference themselves to support recursive queries -- something a plain subquery cannot do. A single query can define multiple CTEs, and later CTEs can reference earlier ones.
Cricket analogy: Defining a WITH clause named top_scorers is like naming a temporary highlights reel from the match that later commentary can reference by name, and a later CTE like top_scorers_by_venue can build on it, unlike a nameless replay clip.
Syntax
-- Basic (non-recursive) CTE
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;
-- Recursive CTE
WITH RECURSIVE org_chart AS (
-- Anchor member
SELECT emp_id, manager_id, name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member
SELECT e.emp_id, e.manager_id, e.name, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT * FROM org_chart ORDER BY level, emp_id;Explanation
A recursive CTE must have exactly two parts combined with UNION ALL: an anchor member (a non-recursive SELECT that seeds the initial rows) and a recursive member (a SELECT that references the CTE's own name and joins it to the base table to produce the next level). Execution proceeds iteratively: the anchor runs once, then the recursive member repeatedly runs against only the newly produced rows from the previous iteration, accumulating results until it produces no new rows. UNION ALL (not UNION) is required so duplicate rows are not silently discarded and the recursion can terminate correctly; using UNION can also mask infinite loops by deduplicating identical rows.
Cricket analogy: A recursive CTE tracing a bowling lineage works like an anchor pick of the current bowler, then a recursive step repeatedly adding the next over's bowler until the innings ends; using UNION ALL instead of UNION matters because two overs might legitimately share the same bowler and shouldn't be deduplicated away.
Example
-- Multiple CTEs, one referencing another
WITH high_earners AS (
SELECT emp_id, name, dept_id, salary
FROM employees
WHERE salary > 80000
),
high_earner_counts AS (
SELECT dept_id, COUNT(*) AS num_high_earners
FROM high_earners
GROUP BY dept_id
)
SELECT d.dept_name, hec.num_high_earners
FROM high_earner_counts hec
JOIN departments d ON d.dept_id = hec.dept_id
ORDER BY hec.num_high_earners DESC;Output
The recursive org_chart example returns every employee reachable from a top-level manager (manager_id IS NULL), each row annotated with its depth in the hierarchy via the 'level' column, ordered top-down. The multi-CTE example returns one row per department that has at least one high earner, with a count column, sorted from the most high earners to the fewest -- each CTE acts as a readable, named building block rather than nesting nested subqueries.
Cricket analogy: The recursive query returning every player under a head coach with a level column showing reporting depth is like listing every fielder under a captain down through vice-captain and squad tiers, ordered top-down by seniority; a second multi-CTE query then counts, per team, how many players exceed a run-scoring threshold, sorted from most to fewest.
Key Takeaways
- A CTE is defined with WITH and exists only for the duration of the statement that follows it.
- CTEs improve readability by naming intermediate result sets and allowing later CTEs to reference earlier ones.
- A recursive CTE requires WITH RECURSIVE, an anchor member, UNION ALL, and a recursive member that joins back to the CTE name.
- UNION ALL (not UNION) is required in recursive CTEs to avoid masking infinite loops through deduplication.
- Some databases can inline or materialize CTEs differently, so CTEs are not automatically a performance optimization -- they primarily aid clarity and enable recursion.
Practice what you learned
1. Which keyword introduces a Common Table Expression?
2. What two parts must a recursive CTE contain, combined with UNION ALL?
3. Why is UNION ALL required instead of UNION in a recursive CTE?
4. How long does a non-recursive CTE's result set persist?
Was this page helpful?
You May Also Like
Subqueries in SQL
A query nested inside another query, used in SELECT, FROM, or WHERE clauses to compute intermediate results.
Window Functions
Functions that compute values across a set of related rows without collapsing them into a single output row, using OVER().
INNER JOIN
Combine rows from two tables where the join condition matches in both, discarding unmatched rows.