Common Table Expressions (CTE) in MySQL — Basics & Recursive CTE

A CTE (Common Table Expression) is a temporary named result set that exists only for the duration of a single query. Think of it as a helper query you define once and reuse inside the same statement.

Table of Contents

• CTE Basics
• Multiple CTEs in One Query
• Recursive CTE
• Practical Examples

CTE Basics

A CTE lets you give a subquery a name and then use that name like a table in the rest of your query. It makes complex queries easier to read because you break them into named steps.

Syntax

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

The WITH keyword defines the CTE. The SELECT that follows can reference cte_name as if it were a real table.

Example – Simple CTE

WITH high_earners AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE salary > 100000
)
SELECT * FROM high_earners;

This creates a CTE called high_earners that holds all employees earning over 100k, then queries it. Same result as a subquery or a view, but the CTE only lives for this one statement and keeps the query organized.

Why Use a CTE Instead of a Subquery?

• You can reference the same CTE multiple times in one query — a subquery would be duplicated.
• Queries read top-to-bottom, which is closer to how you think about the logic.
• CTEs can be recursive (more on that below).

Multiple CTEs in One Query

You can define several CTEs in a single WITH clause, separated by commas. Each CTE can reference the ones defined before it.

Syntax

WITH
    cte1 AS (
        SELECT ...
    ),
    cte2 AS (
        SELECT ...
        FROM cte1
    )
SELECT * FROM cte2;

Example – Sales Report

WITH
    regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM sales
        GROUP BY region
    ),
    top_region AS (
        SELECT region, total_sales
        FROM regional_sales
        ORDER BY total_sales DESC
        LIMIT 1
    )
SELECT * FROM top_region;

top_region reuses regional_sales to find the single best-performing region. Without CTEs you'd need a subquery inside another subquery or a temporary table.

Recursive CTE

A recursive CTE is a CTE that calls itself. It is useful for working with hierarchical or tree-structured data — org charts, category trees, bill of materials, and so on.

Syntax

WITH RECURSIVE cte_name AS (
    -- Anchor member: the base result
    SELECT ...
    UNION ALL
    -- Recursive member: references the CTE itself
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT * FROM cte_name;

Two parts:

1. Anchor member — runs first, gives the starting rows.
2. Recursive member — runs repeatedly, each time using the previous iteration's output, until it returns no rows.

UNION ALL combines the results. You can use UNION DISTINCT if you need to remove duplicates, but UNION ALL is faster and more common.

Example – Number Sequence

Generate numbers 1 through 5:

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM numbers
    WHERE n < 5
)
SELECT * FROM numbers;

How it runs:

1. Anchor runs → n = 1.
2. Recursive runs on n = 1 → produces 2.
3. Recursive runs on n = 2 → produces 3.
4. Stops when n >= 5.

Example – Organisation Chart

Given an employees table where each row has a manager_id pointing to another employee:

WITH RECURSIVE org_chart AS (
    -- Anchor: the CEO (no manager)
    SELECT employee_id, first_name, last_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: direct reports of the previous level
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, oc.level + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level, employee_id;

Each iteration peels off one layer of the hierarchy. The level column tracks how many steps from the CEO each person sits.

Important Rules

• Every recursive CTE must have a termination condition — otherwise it runs forever.
• MySQL caps recursion at cte_max_recursion_depth (default 1000). You can raise it:

  SET SESSION cte_max_recursion_depth = 10000;
  

• The recursive member cannot use aggregation (GROUP BY), DISTINCT, ORDER BY, or LIMIT directly — those are applied after the recursion finishes.

Practical Examples

Example – Date Range

Generate a list of all dates in a month:

WITH RECURSIVE dates AS (
    SELECT '2026-04-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY
    FROM dates
    WHERE dt < LAST_DAY('2026-04-01')
)
SELECT * FROM dates;

Useful for filling gaps in reports — left-join your sales data against this to see days with zero sales.

Example – Category Tree (with Path)

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, name AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

The path column builds a breadcrumb like Electronics > Laptops > Gaming.