SQL CTEs Explained — WITH Clauses, Recursive Queries, and Best Practices

What a CTE is

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword. It lives only for the duration of the query and can be referenced one or more times in the main SELECT. CTEs do not materialise to disk — the database folds them into the execution plan like a derived table (with some exceptions in Postgres and SQL Server where they act as optimisation fences).

Basic syntax

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

Breaking complex queries into readable steps

The biggest win from CTEs is readability. Instead of nesting subqueries three levels deep, you define each step with a descriptive name.

-- Find customers at risk of churn:
-- 1. Calculate each customer's last order date
-- 2. Flag those with no order in the past 90 days
-- 3. Join back to customer details for the output

WITH last_orders AS (
    SELECT
        customer_id,
        MAX(created_at) AS last_order_date
    FROM orders
    GROUP BY customer_id
),
at_risk AS (
    SELECT customer_id
    FROM   last_orders
    WHERE  last_order_date < NOW() - INTERVAL '90 days'
)
SELECT
    c.id,
    c.email,
    c.created_at      AS joined_at,
    lo.last_order_date
FROM at_risk   ar
JOIN customers  c  ON c.id = ar.customer_id
JOIN last_orders lo ON lo.customer_id = ar.customer_id
ORDER BY lo.last_order_date ASC;

Without CTEs this would be a triple-nested subquery. With CTEs, each step is named and the logic reads like prose.

Multiple CTEs in one query

-- Monthly revenue report with month-over-month growth

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount)               AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
),
with_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    prev_revenue,
    ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS growth_pct
FROM with_growth
ORDER BY month;

Each CTE references only previously defined CTEs or base tables — they are evaluated in declaration order.

Recursive CTEs — traversing hierarchies

A recursive CTE references itself, enabling traversal of tree and graph structures stored in a table (org charts, category trees, bill of materials).

-- Org chart: find all direct and indirect reports under manager id = 5

WITH RECURSIVE org_tree AS (
    -- Anchor: start with the manager
    SELECT id, name, manager_id, 1 AS depth
    FROM   employees
    WHERE  id = 5

    UNION ALL

    -- Recursive step: add their reports
    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 id, name, depth
FROM   org_tree
ORDER  BY depth, name;

-- Category breadcrumb path (e-commerce category tree)
WITH RECURSIVE category_path AS (
    SELECT id, name, parent_id, name::TEXT AS path
    FROM   categories
    WHERE  id = 42   -- leaf category

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           c.name || ' > ' || cp.path
    FROM   categories    c
    JOIN   category_path cp ON cp.id = c.parent_id
    WHERE  c.parent_id IS NOT NULL
)
SELECT path FROM category_path ORDER BY id LIMIT 1;
-- → 'Electronics > Cameras > Mirrorless'

Add a depth counter and a WHERE depth < n guard in the recursive step to prevent infinite loops on graphs with cycles.

CTE vs subquery vs temp table

Use CTEs for readability and for recursive queries. Use temp tables when the intermediate result is large, used many times, or needs an index.

Recap

CTEs (the WITH clause) name intermediate result sets so complex queries read as a sequence of labelled steps. Chain multiple CTEs to build up logic without nesting subqueries. Recursive CTEs are the standard way to traverse parent-child relationships in SQL. Choose a temp table over a CTE when you need to index the intermediate result or reuse it across multiple statements.