Common Table Expressions (CTEs)

Writing SQL That Humans Can Read

As queries grow in complexity, nesting subqueries inside subqueries creates code that is nearly impossible to debug or maintain:

-- Hard to read: what does this even do?
SELECT region FROM (
    SELECT region, SUM(amount) AS total
    FROM (
        SELECT * FROM orders WHERE status = 'completed'
    ) AS completed_orders
    GROUP BY region
) AS regional_totals
WHERE total > 100000;

Common Table Expressions (CTEs), defined with the WITH keyword, solve this by letting you name intermediate result sets and reference them by name — like defining variables at the top of a function.

WITH completed_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
),
regional_totals AS (
    SELECT region, SUM(amount) AS total
    FROM   completed_orders
    GROUP BY region
),
top_regions AS (
    SELECT region
    FROM   regional_totals
    WHERE  total > 100000
)
SELECT * FROM top_regions;

This reads like a story: "First, get completed orders. Then, sum them by region. Then, keep only the top regions. Finally, show me the result."

Recursive CTEs

CTEs have a powerful variant: recursive CTEs, which allow a query to reference itself. This is the standard SQL way to traverse hierarchical data — org charts, file system trees, bill-of-materials structures.

-- Walk an org chart: find all reports under manager_id = 1
WITH RECURSIVE org_tree AS (
    -- Base case: start with the top-level manager
    SELECT id, name, manager_id, 1 AS depth
    FROM   employees
    WHERE  id = 1

    UNION ALL

    -- Recursive case: find employees who report to someone already in the tree
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM   employees e
    JOIN   org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth;

The recursive CTE has two parts separated by UNION ALL: the base case (the starting row) and the recursive case (how to find the next level). The database repeats the recursive case until no new rows are added.

A critical safety note: always include a depth counter or a WHERE depth < N guard in recursive CTEs. A cycle in the data (employee A reports to B, B reports to A) will cause infinite recursion and crash the query.