SQL CTEs — Common Table Expressions Explained

Overview

Common Table Expressions (CTEs), introduced in SQL:1999, provide a named temporary result set that exists for the duration of a single query. They improve readability by breaking complex queries into named blocks, enable recursion for hierarchical data, and can be materialized for performance. Supported by PostgreSQL, SQL Server, MySQL 8+, Oracle, and SQLite 3.8.3+.

When to Use

• For alternatives, see Complete Guide to SQL Query Optimization.

• A query has multiple levels of nested subqueries

• You need to reference the same subquery multiple times

• Hierarchical data must be traversed (org charts, bill of materials, threaded comments)

• Query logic needs to be self-documenting and modular

• You want to build complex queries incrementally and test each part

Basic CTE Syntax

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

Non-Recursive CTE Example

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(total) as revenue,
        COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
),
avg_sales AS (
    SELECT AVG(revenue) as avg_revenue FROM monthly_sales
)
SELECT
    ms.month,
    ms.revenue,
    ms.order_count,
    a.avg_revenue,
    ms.revenue - a.avg_revenue as variance
FROM monthly_sales ms
CROSS JOIN avg_sales a
ORDER BY ms.month;

Recursive CTE for Hierarchies

-- Org chart: find all reports under a manager
WITH RECURSIVE org_tree AS (
    -- Anchor: start with the manager
    SELECT id, name, manager_id, 1 as depth
    FROM employees
    WHERE id = 1  -- CEO

    UNION ALL

    -- Recursive: find direct reports
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth, name;

CTE vs Subquery

Aspect	CTE	Subquery
Readability	Named, reusable	Inline, anonymous
Reusability	Can reference multiple times	Must duplicate if used again
Recursion	Supported	Not supported
Materialization	Can be materialized (PostgreSQL)	Evaluated each time

Multiple CTEs

WITH
    active_users AS (
        SELECT user_id, last_login
        FROM users
        WHERE last_login >= CURRENT_DATE - INTERVAL '30 days'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) as order_count, SUM(total) as lifetime_value
        FROM orders
        WHERE user_id IN (SELECT user_id FROM active_users)
        GROUP BY user_id
    )
SELECT
    u.user_id,
    u.last_login,
    COALESCE(o.order_count, 0) as order_count,
    COALESCE(o.lifetime_value, 0) as lifetime_value
FROM active_users u
LEFT JOIN user_orders o ON u.user_id = o.user_id;

Materialized CTEs (PostgreSQL)

WITH regional_sales AS MATERIALIZED (
    SELECT region, SUM(total) as total_sales
    FROM orders
    GROUP BY region
    HAVING SUM(total) > 1000000
)
SELECT * FROM regional_sales;

Common Mistakes

• Infinite recursion — recursive CTEs without a proper termination condition will error or loop forever
• Treating CTEs as temp tables — they are query-scoped; for temp tables, use CREATE TEMP TABLE
• Performance assumptions — in some engines, CTEs are inlined; in others, they may materialize. Profile your query.
• Over-nesting CTEs — deeply nested CTEs can become harder to read than the original subquery soup
• Mutual recursion — not supported in most databases; use iterative approaches instead

Troubleshooting

• Query is slow after an index change: check execution plans and cardinality estimates. Rebuild statistics and verify the index is being used.
• Replication lag grows: monitor network, disk I/O, and long transactions. Split large writes and consider parallel replication.
• Connections exhausted: review connection pool size, idle timeouts, and leaked connections. Use prepared statements and close connections in finally blocks.
• Backup takes too long: enable compression, incremental backups, and off-peak scheduling. Test restore times against RTO targets.
• Deadlocks in high concurrency: access tables and rows in a consistent order. Keep transactions short and retry deadlocked operations.

FAQ

Do CTEs improve performance? Not inherently. They improve readability and maintainability. In PostgreSQL, MATERIALIZED CTEs can improve performance by evaluating once. In SQL Server, CTEs are usually inlined.

Can I use CTEs in UPDATE or DELETE? Yes, in PostgreSQL and SQL Server: WITH cte AS (...) UPDATE table SET ... FROM cte WHERE ....

Are CTEs available in MySQL? Yes, non-recursive CTEs in MySQL 8.0+, recursive in MySQL 8.0+ with WITH RECURSIVE.

How do I get started with this in an existing project?

Start with a small, isolated part of your codebase. Apply the concepts from this guide to one module or service. Measure the impact, then expand to other areas.

What tools do I need?

The tools mentioned throughout this guide are listed in each section. Most are open-source and widely adopted. Check the related resources for setup instructions.

How do I measure success after implementing this?

Define clear metrics before starting: performance benchmarks, error rates, or maintainability indicators. Compare before and after. Iterate based on the data, not on assumptions.

Advanced Topics

Detailed Scenario: Employee Hierarchy with Recursive CTE

-- Structure: org chart with 5 levels of depth
-- Table: employees(id, name, manager_id, salary, department)

-- 1. Find all direct and indirect reports of the CEO
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, salary, 1 AS depth,
           ARRAY[id] AS path
    FROM employees
    WHERE manager_id IS NULL  -- CEO has no manager

    UNION ALL

    SELECT e.id, e.name, e.manager_id, e.salary,
           ot.depth + 1,
           ot.path || e.id
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.depth < 10  -- Safety limit
)
SELECT
    id,
    name,
    depth,
    path,
    salary,
    (SELECT name FROM employees m WHERE m.id = ot.manager_id) AS manager_name
FROM org_tree ot
ORDER BY path;

-- 2. Calculate total budget per org chart branch
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, salary AS total_budget, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id,
           ot.total_budget + e.salary,
           ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, total_budget, depth
FROM org_tree
WHERE depth <= 3
ORDER BY total_budget DESC;

-- 3. Find chain of command from an employee to the CEO
WITH RECURSIVE chain_of_command AS (
    SELECT id, name, manager_id, 1 AS steps_to_ceo
    FROM employees
    WHERE id = 42  -- Specific employee

    UNION ALL

    SELECT e.id, e.name, e.manager_id, coc.steps_to_ceo + 1
    FROM employees e
    INNER JOIN chain_of_command coc ON e.id = coc.manager_id
)
SELECT name, steps_to_ceo
FROM chain_of_command
ORDER BY steps_to_ceo;

-- 4. Bill of Materials: component explosion
-- Table: bom(product_id, component_id, quantity)
WITH RECURSIVE bom_explosion AS (
    SELECT
        product_id,
        component_id,
        quantity,
        1 AS level,
        CAST(quantity AS FLOAT) AS total_quantity,
        CAST(component_id AS VARCHAR(1000)) AS component_path
    FROM bom
    WHERE product_id = 100  -- Final product

    UNION ALL

    SELECT
        b.product_id,
        b.component_id,
        b.quantity,
        be.level + 1,
        be.total_quantity * b.quantity,
        be.component_path || '>' || b.component_id
    FROM bom b
    INNER JOIN bom_explosion be ON b.product_id = be.component_id
    WHERE be.level < 20  -- Depth limit
)
SELECT
    level,
    component_id,
    total_quantity,
    component_path
FROM bom_explosion
ORDER BY component_path;

How do I optimize recursive CTEs on large datasets?

Add a depth limit (WHERE depth < N) to prevent infinite recursion. Use indexes on the join column (manager_id, product_id). In PostgreSQL, consider materializing the CTE with the MATERIALIZED clause if it is referenced multiple times. For very deep hierarchies (>1000 levels), consider storing the materialized path (path enumeration) in an additional column to avoid recursion on every query.

End of document. Review and update quarterly.

Common Production Pitfalls

• Treating the guide as a checklist to complete once rather than a practice to evolve.
• Adopting every recommendation at once instead of starting with one measured change.
• Skipping the maturity assessment and forcing advanced practices on an unprepared team.
• Not updating runbooks and on-call expectations as new practices are introduced.
• Ignoring real incident data when prioritizing which parts of the guide to apply first.
• Failing to assign an owner who reviews decisions quarterly.
• Copying examples without adapting them to the team’s actual tooling and constraints.
• Forgetting to measure outcomes before adding the next improvement.