CTE Chains and Readability

Why CTEs Exist

A Common Table Expression (CTE) is a named temporary result set that exists only for the duration of a single query. It is defined with the WITH clause at the top of the statement and referenced by name in the main query. CTEs do not store data — they are a syntactic tool for organizing logic.

The problem CTEs solve is readability under nesting. Consider a query that finds departments where the average salary exceeds the company average. Written with nested subqueries, it looks like this:

SELECT department, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avgs
WHERE avg_salary > (
    SELECT AVG(salary) FROM employees
);

This works, but the logic flows outward-in: you read the outer WHERE, then jump to the FROM subquery, then jump again to the scalar subquery. With three or more levels of nesting, the query becomes difficult to parse. A CTE rewrites the same logic as a top-to-bottom pipeline:

WITH dept_avgs AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
company_avg AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT d.department, d.avg_salary
FROM dept_avgs d
CROSS JOIN company_avg c
WHERE d.avg_salary > c.avg_salary;

Now the query reads like a recipe: first compute department averages, then compute the company average, then join and filter. Each step is a named block that can be understood in isolation. This is the primary value of CTEs — not performance, but cognitive load reduction.

A CTE is defined once and can be referenced multiple times in the main query, just like a table. The name is scoped to the single statement that follows the WITH clause. Once the statement finishes, the CTE disappears — it is not stored in the database and cannot be referenced by later queries.

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) AS headcount
FROM high_earners
GROUP BY department;

The syntax is consistent across PostgreSQL, SQL Server, MySQL 8+, SQLite, and BigQuery. Oracle uses the same WITH syntax. This portability makes CTEs a safe default for production SQL.

Chaining CTEs into Pipelines

The real power of CTEs emerges when you chain them — each CTE references the one before it, forming a pipeline where the output of one step becomes the input to the next. This mirrors how you think about a multi-step analysis: clean the data, then aggregate, then filter, then join.

Consider a realistic analytics question: for each product category, find the top-selling product by revenue, along with its share of category revenue. This requires at least three steps: compute revenue per product, rank products within each category, and calculate the category total for the share. As a chain of CTEs:

WITH product_revenue AS (
    SELECT
        p.product_id,
        p.product_name,
        p.category_id,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.product_name, p.category_id
),
category_totals AS (
    SELECT category_id, SUM(revenue) AS category_revenue
    FROM product_revenue
    GROUP BY category_id
),
ranked AS (
    SELECT
        pr.*,
        ct.category_revenue,
        RANK() OVER (PARTITION BY pr.category_id ORDER BY pr.revenue DESC) AS revenue_rank
    FROM product_revenue pr
    JOIN category_totals ct ON ct.category_id = pr.category_id
)
SELECT product_name, category_id, revenue, category_revenue,
       ROUND(100.0 * revenue / category_revenue, 2) AS pct_of_category
FROM ranked
WHERE revenue_rank = 1;

Each CTE has a single responsibility. product_revenue joins and aggregates. category_totals rolls up to the category level. ranked combines them and applies a window function. The final SELECT is trivial — it just filters the top row per category and formats the output.

This structure has a practical benefit for debugging. If the final result looks wrong, you can run any CTE in isolation by copying its body into a standalone SELECT. You cannot do this with nested subqueries without rewriting the entire query. In a production environment where queries evolve over weeks, this debuggability is worth more than any marginal performance difference.

Ordering the chain. CTEs must be defined in dependency order — a CTE can only reference CTEs defined above it. The database resolves names at parse time, so referencing a CTE before its definition raises an error. This constraint enforces the top-to-bottom reading order that makes CTEs readable in the first place.

-- Invalid: total_revenue references product_revenue before it is defined
WITH total_revenue AS (
    SELECT SUM(revenue) AS total FROM product_revenue
),
product_revenue AS (
    SELECT product_id, SUM(quantity * price) AS revenue
    FROM order_items GROUP BY product_id
)
SELECT * FROM total_revenue;

The fix is simply to reorder the CTEs so dependencies come first. This is the same principle as defining variables before using them in a programming language.

CTEs vs Subqueries vs Temporary Tables

CTEs, subqueries, and temporary tables all solve the same fundamental problem: breaking a complex query into named steps. Choosing between them is an engineering judgment about scope, performance, and reusability.

Subqueries are anonymous and inline. They are the right choice when the nested logic is simple and used in exactly one place. A subquery inside a WHERE clause that filters on an aggregate is idiomatic and should not be extracted into a CTE just for the sake of it. The moment you find yourself nesting a subquery inside a subquery inside a subquery, the readability cost exceeds the benefit.

CTEs are named and scoped to a single statement. They are the right choice when the same intermediate result is referenced multiple times, or when the query has three or more logical steps. A CTE can also be referenced by name multiple times in the main query, which avoids repeating the same subquery body:

WITH active_users AS (
    SELECT user_id, email FROM users WHERE is_active = TRUE
)
SELECT
    (SELECT COUNT(*) FROM active_users) AS active_count,
    (SELECT COUNT(*) FROM orders o JOIN active_users au ON o.user_id = au.user_id) AS orders_from_active;

Temporary tables are physical objects stored in the database (usually in a temp schema) that persist for the duration of a session. They are the right choice when the intermediate result is large, referenced across multiple statements, or needs indexes for performance. Unlike CTEs, temporary tables can be indexed, and the database's statistics collector can gather statistics on them, which helps the optimizer make better plans.

-- Temporary table example (SQL Server syntax)
SELECT user_id, SUM(amount) AS total_spend
INTO #user_spend
FROM orders
GROUP BY user_id;

CREATE INDEX idx_user_spend ON #user_spend(user_id);

SELECT u.username, us.total_spend
FROM users u
JOIN #user_spend us ON us.user_id = u.user_id;

The materialization question. A common misconception is that CTEs are always materialized — computed once and stored in memory. In reality, most databases treat CTEs as syntactic sugar and inline them into the main query, which means a CTE referenced multiple times may be recomputed each time. PostgreSQL 12+ changed this with the MATERIALIZED keyword, which forces the CTE to be computed once:

WITH expensive_lookup AS MATERIALIZED (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT * FROM expensive_lookup WHERE order_count > 10;

The practical rule: if a CTE is referenced multiple times and its computation is expensive, either force materialization (PostgreSQL) or use a temporary table. For most analytical queries on moderate data, the default inlining is fine and often faster because the optimizer can push predicates and eliminate unnecessary columns.

Recursive CTEs and Hierarchical Data

A recursive CTE references itself, enabling queries that traverse hierarchical or graph-structured data in a single statement. This is the only way to walk a tree in standard SQL without procedural code.

A recursive CTE has two parts: an anchor member (the base case) and a recursive member (which references the CTE name). The database executes the anchor, then repeatedly executes the recursive member against the accumulated result until no new rows are produced.

Consider an employee hierarchy where each employee has a manager_id pointing to another employee. To find the full chain of command above a given employee:

WITH RECURSIVE chain AS (
    -- Anchor: the starting employee
    SELECT employee_id, first_name, last_name, manager_id, 1 AS depth
    FROM employees
    WHERE employee_id = 42

    UNION ALL

    -- Recursive: join the manager of the current row
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, c.depth + 1
    FROM employees e
    JOIN chain c ON e.employee_id = c.manager_id
)
SELECT * FROM chain ORDER BY depth;

The anchor selects employee 42. The recursive member joins the chain CTE back to the employees table, finding the manager of each row already in the chain. Each iteration adds one level. The depth column tracks how many levels up the hierarchy we have climbed. The recursion stops when a row's manager_id is NULL (the CEO has no manager) or when no new join is found.

The UNION ALL vs UNION distinction. UNION ALL accumulates all rows, including duplicates. UNION deduplicates, which can cause the recursion to terminate early if the hierarchy contains cycles (an employee who is their own indirect manager). For tree structures without cycles, UNION ALL is correct and faster. For graphs with possible cycles, use UNION or add a cycle-detection column:

WITH RECURSIVE walk AS (
    SELECT employee_id, manager_id, CAST(employee_id AS VARCHAR(MAX)) AS path
    FROM employees WHERE employee_id = 1

    UNION ALL

    SELECT e.employee_id, e.manager_id,
           w.path + ',' + CAST(e.employee_id AS VARCHAR(MAX))
    FROM employees e
    JOIN walk w ON e.employee_id = w.manager_id
    WHERE w.path NOT LIKE '%,' + CAST(e.employee_id AS VARCHAR(MAX)) + ',%'
)
SELECT * FROM walk;

The path column tracks visited nodes, and the WHERE clause prevents revisiting any node. This pattern converts a potentially infinite recursion into a bounded traversal.

Recursive CTEs are the standard solution for bill-of-materials explosions, org charts, category trees, and any adjacency-list traversal. Most databases (PostgreSQL, SQL Server, MySQL 8+, SQLite, BigQuery) support them with minor syntax variations — the RECURSIVE keyword after WITH is required in PostgreSQL and SQLite but optional in SQL Server.