Common Table Expressions (CTEs) in SQL

A Common Table Expression (CTE) is a named temporary result set defined within a single SQL statement. It exists only for the duration of that statement, making complex queries readable by breaking them into logical steps. Think of a CTE as a named subquery that you define at the top and reference in the main query.

Basic CTE syntax

The WITH keyword introduces a CTE. You give it a name, define its query, and reference it in the main statement.

WITH active_users AS (
    SELECT id, name, email, last_login
    FROM users
    WHERE status = 'active'
      AND last_login > CURRENT_DATE - INTERVAL '30 days'
)
SELECT name, email
FROM active_users
ORDER BY last_login DESC;

This is equivalent to a subquery, but the intent is clearer. The CTE name (active_users) documents what the intermediate result represents.

Why CTEs improve readability

Compare a nested subquery approach with a CTE approach for the same problem: finding the top 5 customers by total spending in the last quarter, along with their most recent order date.

-- Subquery approach: hard to follow
SELECT c.name, sub.total_spent, sub.last_order
FROM customers c
JOIN (
    SELECT customer_id,
           SUM(total) AS total_spent,
           MAX(order_date) AS last_order
    FROM orders
    WHERE order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
      AND order_date < DATE_TRUNC('quarter', CURRENT_DATE)
    GROUP BY customer_id
    ORDER BY total_spent DESC
    LIMIT 5
) sub ON sub.customer_id = c.id
ORDER BY sub.total_spent DESC;

-- CTE approach: reads top to bottom
WITH quarterly_spending AS (
    SELECT
        customer_id,
        SUM(total) AS total_spent,
        MAX(order_date) AS last_order
    FROM orders
    WHERE order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
      AND order_date < DATE_TRUNC('quarter', CURRENT_DATE)
    GROUP BY customer_id
),
top_spenders AS (
    SELECT customer_id, total_spent, last_order
    FROM quarterly_spending
    ORDER BY total_spent DESC
    LIMIT 5
)
SELECT c.name, t.total_spent, t.last_order
FROM customers c
JOIN top_spenders t ON t.customer_id = c.id
ORDER BY t.total_spent DESC;

The CTE version has more lines, but each step is named and self-contained. You can read it top to bottom: first we calculate spending, then we pick the top 5, then we join with customer names.

Chaining multiple CTEs

You can define multiple CTEs separated by commas. Each CTE can reference the ones defined before it.

WITH
-- Step 1: Get monthly revenue per product
monthly_revenue AS (
    SELECT
        product_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(quantity * unit_price) AS revenue
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE o.order_date >= '2025-01-01'
    GROUP BY product_id, DATE_TRUNC('month', order_date)
),
-- Step 2: Calculate month-over-month growth
revenue_growth AS (
    SELECT
        product_id,
        month,
        revenue,
        LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS prev_revenue,
        CASE
            WHEN LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) > 0
            THEN ROUND(
                (revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month))
                / LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) * 100, 1
            )
            ELSE NULL
        END AS growth_pct
    FROM monthly_revenue
),
-- Step 3: Find products with consistent growth
growing_products AS (
    SELECT product_id
    FROM revenue_growth
    WHERE month >= '2025-03-01'
      AND growth_pct > 0
    GROUP BY product_id
    HAVING COUNT(*) = (
        SELECT COUNT(DISTINCT month)
        FROM revenue_growth
        WHERE month >= '2025-03-01'
    )
)
-- Final: join with product details
SELECT p.name, p.category, rg.month, rg.revenue, rg.growth_pct
FROM growing_products gp
JOIN products p ON p.id = gp.product_id
JOIN revenue_growth rg ON rg.product_id = gp.product_id
ORDER BY p.name, rg.month;

Each CTE builds on the previous one. Without CTEs, this would be a deeply nested mess of subqueries that is nearly impossible to debug.

CTEs in INSERT, UPDATE, DELETE

CTEs are not limited to SELECT statements. They work with data modification statements too.

-- CTE with INSERT: archive old orders and insert a summary
WITH archived AS (
    DELETE FROM orders
    WHERE status = 'completed'
      AND order_date < CURRENT_DATE - INTERVAL '2 years'
    RETURNING *
)
INSERT INTO orders_archive (id, customer_id, total, order_date, archived_at)
SELECT id, customer_id, total, order_date, NOW()
FROM archived;

-- CTE with UPDATE: update prices based on a calculation
WITH price_adjustments AS (
    SELECT
        p.id,
        CASE
            WHEN s.units_sold > 1000 THEN p.price * 0.95  -- 5% discount for hot sellers
            WHEN s.units_sold < 10 THEN p.price * 1.10     -- 10% increase for slow movers
            ELSE p.price
        END AS new_price
    FROM products p
    JOIN sales_summary s ON s.product_id = p.id
    WHERE s.period = 'last_quarter'
)
UPDATE products p
SET price = pa.new_price
FROM price_adjustments pa
WHERE p.id = pa.id
  AND p.price != pa.new_price;

Recursive CTEs

Recursive CTEs are the killer feature. They let you query hierarchical or graph-structured data by defining a base case and a recursive step.

-- Organization hierarchy: find all reports under a manager
WITH RECURSIVE org_tree AS (
    -- Base case: the starting manager
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1  -- CEO

    UNION ALL

    -- Recursive step: find direct reports of each person found so far
    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
    REPEAT('  ', depth) || name AS org_chart,
    depth
FROM org_tree
ORDER BY depth, name;

org_chart              | depth
-----------------------|------
Alice (CEO)            | 0
  Bob (VP Eng)         | 1
  Carol (VP Sales)     | 1
    Dave (Sr Engineer)  | 2
    Eve (Sales Lead)   | 2
      Frank (SDR)      | 3

The recursive CTE has two parts connected by UNION ALL:

1. Anchor member — The base case that produces the initial rows
2. Recursive member — Joins with the CTE itself to produce additional rows

Execution stops when the recursive member produces no new rows.

Generating sequences

-- Generate a date series (useful for filling gaps in time-series data)
WITH RECURSIVE date_series AS (
    SELECT DATE '2025-01-01' AS day

    UNION ALL

    SELECT day + INTERVAL '1 day'
    FROM date_series
    WHERE day < DATE '2025-12-31'
)
SELECT
    ds.day,
    COALESCE(COUNT(o.id), 0) AS order_count
FROM date_series ds
LEFT JOIN orders o ON DATE(o.order_date) = ds.day
GROUP BY ds.day
ORDER BY ds.day;

Graph traversal: finding paths

-- Find all paths between two cities in a route network
WITH RECURSIVE routes AS (
    -- Start from the origin city
    SELECT
        destination AS current_city,
        ARRAY[origin, destination] AS path,
        distance AS total_distance,
        1 AS hops
    FROM flights
    WHERE origin = 'NYC'

    UNION ALL

    -- Extend the path to the next city
    SELECT
        f.destination,
        r.path || f.destination,
        r.total_distance + f.distance,
        r.hops + 1
    FROM routes r
    JOIN flights f ON f.origin = r.current_city
    WHERE f.destination != ALL(r.path)  -- Prevent cycles
      AND r.hops < 5                     -- Limit depth
)
SELECT path, total_distance, hops
FROM routes
WHERE current_city = 'LAX'
ORDER BY total_distance
LIMIT 5;

The WHERE f.destination != ALL(r.path) check prevents infinite loops by ensuring we never revisit a city.

Bill of materials

-- Bill of materials: explode a product into all its components
WITH RECURSIVE bom AS (
    -- Base: top-level product
    SELECT
        component_id,
        quantity,
        1 AS level
    FROM product_components
    WHERE product_id = 100

    UNION ALL

    -- Recursive: sub-components
    SELECT
        pc.component_id,
        bom.quantity * pc.quantity,
        bom.level + 1
    FROM bom
    JOIN product_components pc ON pc.product_id = bom.component_id
    WHERE bom.level < 10  -- Safety limit
)
SELECT
    c.name AS component,
    SUM(bom.quantity) AS total_needed,
    MAX(bom.level) AS deepest_level
FROM bom
JOIN components c ON c.id = bom.component_id
GROUP BY c.name
ORDER BY total_needed DESC;

Materialization behavior

Some databases materialize CTEs (compute them once and store the result), while others inline them (treat them like subqueries and potentially re-evaluate).

PostgreSQL 12+ — The planner decides whether to materialize. You can force it with MATERIALIZED or NOT MATERIALIZED.

-- Force materialization: compute once, even if referenced once
WITH active_users AS MATERIALIZED (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE name LIKE 'A%';

-- Force inlining: let the planner push filters into the CTE
WITH active_users AS NOT MATERIALIZED (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE name LIKE 'A%';

With NOT MATERIALIZED, the planner can push the LIKE 'A%' filter into the CTE, potentially using an index on name. With MATERIALIZED, it computes all active users first, then filters.

MySQL 8.0+ — CTEs are generally inlined (merged into the outer query) unless they are recursive or referenced multiple times.

SQL Server — CTEs are always inlined. They never create temporary tables.

CTEs vs subqueries vs temp tables

Feature	CTE	Subquery	Temp Table
Readability	Excellent	Poor for complex queries	Good
Scope	Single statement	Single statement	Session
Indexable	No	No	Yes
Reusable in statement	Yes (multiple refs)	No (must repeat)	Yes
Recursive	Yes	No	No
Performance	Depends on DB	Same as CTE usually	Best for repeated access

-- Use a temp table when you need to query the same result set
-- multiple times across different statements
CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

CREATE INDEX idx_temp_active_name ON temp_active_users(name);

-- Now query it multiple times efficiently
SELECT * FROM temp_active_users WHERE name LIKE 'A%';
SELECT COUNT(*) FROM temp_active_users;

DROP TABLE temp_active_users;

Use CTEs for readability within a single statement, temp tables for results you need across multiple statements, and subqueries when the logic is simple enough that a CTE would be overkill.

Common mistakes with CTEs

Overusing CTEs for simple queries

-- Overkill: CTE adds complexity without benefit
WITH filtered AS (
    SELECT * FROM users WHERE active = true
)
SELECT name FROM filtered;

-- Just write this:
SELECT name FROM users WHERE active = true;

Not adding depth limits to recursive CTEs

-- Dangerous: can run forever with circular data
WITH RECURSIVE tree AS (
    SELECT id, parent_id FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

-- Safe: add a depth limit
WITH RECURSIVE tree AS (
    SELECT id, parent_id, 0 AS depth FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
    WHERE t.depth < 100
)
SELECT * FROM tree;

CTEs are one of SQL’s most valuable readability tools and, with recursion, one of its most powerful features. Use them to break complex queries into named steps that read like prose, and reach for recursive CTEs whenever you encounter hierarchical or graph-structured data.