Common Table Expressions (CTEs) · SQL & Databases

A CTE names a query so you can build complex logic in readable steps — and, when recursive, walk hierarchies like org charts.

A Common Table Expression (CTE) is a named temporary result defined with WITH, used by the query that follows. CTEs make complex queries readable by breaking them into named steps, and a recursive CTE can traverse hierarchical data such as an employee-manager tree.

WITH ... AS (...)

A CTE reads top to bottom: define one or more named subqueries with WITH name AS (...), then reference them in the main query as if they were tables. It's clearer than nesting subqueries and the name can be reused within the statement.

A CTE for readability

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM   employees
    GROUP  BY dept_id
)
SELECT e.name, e.salary, da.avg_sal
FROM   employees e
JOIN   dept_avg da ON e.dept_id = da.dept_id
WHERE  e.salary > da.avg_sal;

A recursive CTE: the management chain

WITH RECURSIVE chain AS (
    SELECT emp_id, name, manager_id, 1 AS lvl   -- anchor: top managers
    FROM   employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.name, e.manager_id, c.lvl + 1
    FROM   employees e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT * FROM chain ORDER BY lvl;

⚡ The edge

A CTE is a named subquery — it doesn't make queries faster, it makes them readable and avoids repeating the same subquery twice.
A recursive CTE has an anchor member (the starting rows) and a recursive member that joins back to the CTE, combined with UNION ALL — ideal for trees and hierarchies. It must eventually stop.

Worked example

Why use a CTE instead of a nested subquery?

A deeply nested subquery is hard to read and may repeat the same logic.
A CTE names that logic once at the top, so the main query reads cleanly and can reference it multiple times.
It's about clarity and reuse, not performance — the optimiser often treats them similarly.

Answer: For readability and reuse: a CTE names a subquery once so the main query stays clear.

Worked example

What two parts make up a recursive CTE?

The anchor member selects the starting rows (e.g. top-level managers).
The recursive member references the CTE itself to extend the result one level at a time.
They're combined with UNION ALL, and recursion stops when the recursive member returns no new rows.

Answer: An anchor member (base rows) and a recursive member (references the CTE), joined by UNION ALL.

⚠ Watch out

A CTE's scope is the single statement it precedes — it isn't a stored object like a view.
A recursive CTE must terminate; a wrong join can loop until it hits the recursion limit.
CTEs aren't automatically faster than subqueries — they're for clarity.