Home/Learn/MySQL/Common Table Expressions (CTE)

Common Table Expressions (CTE)

Intermediate

Advanced Queries

WITH clause defines reusable named result sets; recursive CTEs traverse hierarchical data (org charts, bill-of-materials) using a base case and recursive member.

Overview

Common Table Expressions (CTEs), introduced in MySQL 8.0 via the WITH clause, define named temporary result sets scoped to the query. They improve readability by factoring out complex subqueries and enabling reuse within the same query. A regular CTE is equivalent to a derived table (inline view) with a name. A recursive CTE adds a UNION ALL self-referencing member that iterates until the recursion terminates — the standard technique for traversing trees and graphs in SQL without application-side loops. MySQL's CTE optimizer can sometimes materialise the CTE result (like a temp table) or inline it — EXPLAIN will show "Materialize CTE" when it does. The recursion depth limit is controlled by cte_max_recursion_depth (default 1000).

Regular CTE — readability and reuse

A regular CTE names a subquery result. Multiple CTEs can be chained. Unlike a view, a CTE exists only for the duration of the statement.

SQL — chained CTEs for monthly analytics

-- Monthly revenue with CTE (vs nested subquery)
WITH monthly_orders AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS month,
        SUM(total_amount)                AS revenue,
        COUNT(*)                         AS order_count
    FROM orders
    WHERE status = 'COMPLETED'
    GROUP BY month
),
monthly_avg AS (
    SELECT AVG(revenue) AS avg_revenue FROM monthly_orders
)
SELECT
    mo.month,
    mo.revenue,
    mo.order_count,
    ROUND(mo.revenue / ma.avg_revenue * 100, 1) AS pct_of_avg
FROM monthly_orders mo, monthly_avg ma
ORDER BY mo.month;

-- Multiple CTEs: chain them with commas
WITH
  active_users AS (SELECT id FROM users WHERE status = 'active'),
  recent_orders AS (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    WHERE user_id IN (SELECT id FROM active_users)
      AND created_at > NOW() - INTERVAL 30 DAY
    GROUP BY user_id
  )
SELECT u.email, ro.cnt
FROM users u JOIN recent_orders ro ON u.id = ro.user_id
ORDER BY ro.cnt DESC;

Recursive CTE — hierarchical tree traversal

Recursive CTEs have an anchor member (base case) and a recursive member (references the CTE itself). MySQL iterates until the recursive member returns no rows.

SQL — recursive CTE for org chart and BOM

-- Employee hierarchy: find all reports under a manager
-- Table: employees(id, name, manager_id)

WITH RECURSIVE org_chart AS (
    -- Anchor: start with the target manager
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 5  -- CEO / root node

    UNION ALL

    -- Recursive member: join children to current level
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
    CONCAT(REPEAT('  ', depth), name) AS hierarchy,
    depth
FROM org_chart
ORDER BY depth, name;

-- Bill of Materials: sum cost of all sub-components
WITH RECURSIVE bom AS (
    SELECT component_id, parent_id, qty, unit_cost, qty * unit_cost AS total_cost
    FROM parts WHERE parent_id IS NULL  -- top-level product

    UNION ALL

    SELECT p.component_id, p.parent_id, p.qty,
           p.unit_cost, bom.qty * p.qty * p.unit_cost
    FROM parts p INNER JOIN bom ON p.parent_id = bom.component_id
)
SELECT SUM(total_cost) AS product_cost FROM bom;

-- Safety: limit recursion depth
SET SESSION cte_max_recursion_depth = 100;

CTE vs subquery vs temp table — performance

MySQL 8 can materialise CTEs or inline them. Understanding EXPLAIN output guides the right approach.

SQL — EXPLAIN CTE materialisation vs temp table

-- Check if MySQL materialises the CTE (creates temp table)
EXPLAIN FORMAT=TREE
WITH expensive_cte AS (
    SELECT customer_id, SUM(total) AS lifetime_value
    FROM orders GROUP BY customer_id
)
SELECT * FROM expensive_cte WHERE lifetime_value > 10000;
-- Look for: "Materialize CTE expensive_cte" in EXPLAIN output

-- If CTE is referenced multiple times, materialisation is beneficial
-- If referenced once, MySQL may inline it (same as subquery)

-- For very large intermediate results, explicit TEMPORARY TABLE
-- gives you control over indexing
CREATE TEMPORARY TABLE tmp_revenue AS
    SELECT customer_id, SUM(total) AS rev FROM orders GROUP BY customer_id;
CREATE INDEX idx_rev ON tmp_revenue(rev);  -- CTE cannot be indexed
SELECT * FROM tmp_revenue WHERE rev > 10000;
DROP TEMPORARY TABLE tmp_revenue;

Key Points to Remember

1CTEs require MySQL 8.0+ — they do not exist in MySQL 5.x.
2Regular CTEs improve readability and allow reuse within the same query; they do not automatically materialise.
3Recursive CTEs use UNION ALL (not UNION) between anchor and recursive member; UNION would eliminate duplicates unnecessarily.
4Recursion terminates when the recursive member returns zero rows — ensure your join condition narrows with each iteration.
5cte_max_recursion_depth (default 1000) prevents infinite loops from data cycles; always validate input trees.
6For large CTEs referenced multiple times, MySQL materialises them into a temp table — verify with EXPLAIN FORMAT=TREE.

Interview Questions

How would you traverse an employee hierarchy stored as an adjacency list in MySQL?

MediumAmazon

What is the difference between a regular CTE and a recursive CTE?

EasyGoogle

When does MySQL materialise a CTE and how can you verify it with EXPLAIN?

HardUber

How would you detect a cycle in a recursive CTE traversal to prevent infinite recursion?

HardMeta

Compare CTEs, derived tables (inline views), and temporary tables — when would you choose each?

MediumNetflix

Ask Aria about Common Table Expressions (CTE)

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Window Functions

Stored Procedures