CTEs & recursive CTEs

Readable queries with WITH, chaining CTEs, and walking hierarchies and series with WITH RECURSIVE.

A Common Table Expression (CTE) gives a name to a subquery so you can use it like a table in the rest of the statement. CTEs make complex queries read top to bottom, like a recipe. Recursive CTEs go further: they can walk trees and graphs, and generate series of numbers or dates. CTEs need MySQL 8.0 or newer.

Your first CTE#

SQL

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer VARCHAR(20) NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(8,2) NOT NULL
);
INSERT INTO orders VALUES
    (1, 'Asha',  '2026-01-05', 120.00), (2, 'Rohan', '2026-01-17',  80.00),
    (3, 'Asha',  '2026-02-03', 260.00), (4, 'Meera', '2026-02-11',  40.00),
    (5, 'Rohan', '2026-02-25', 310.00), (6, 'Asha',  '2026-03-09',  75.00);

Which customers spend more than the average customer?

SQL

WITH customer_totals AS (
    SELECT customer, SUM(total) AS spent
    FROM orders
    GROUP BY customer
)
SELECT customer, spent
FROM customer_totals
WHERE spent > (SELECT AVG(spent) FROM customer_totals)
ORDER BY spent DESC;

Output

+----------+--------+
| customer | spent  |
+----------+--------+
| Asha     | 455.00 |
| Rohan    | 390.00 |
+----------+--------+

WITH customer_totals AS (...) defines a temporary result that exists only for this statement. We used it twice, once in FROM and once in the subquery, without repeating the aggregation. That's something a derived table can't do.

Chaining several CTEs#

Separate CTEs with commas. Each one can use the ones defined before it:

SQL

WITH monthly AS (
    SELECT DATE_FORMAT(ordered_on, '%Y-%m') AS month, SUM(total) AS revenue
    FROM orders
    GROUP BY month
),
ranked AS (
    SELECT month, revenue,
           revenue - (SELECT AVG(revenue) FROM monthly) AS vs_avg
    FROM monthly
)
SELECT * FROM ranked ORDER BY month;

Output

+---------+---------+-------------+
| month   | revenue | vs_avg      |
+---------+---------+-------------+
| 2026-01 |  200.00 |  -95.000000 |
| 2026-02 |  610.00 |  315.000000 |
| 2026-03 |   75.00 | -220.000000 |
+---------+---------+-------------+

Compare that with the same logic as nested derived tables: you'd have to read it inside-out. CTEs let you build and test a query step by step. Write the first CTE, SELECT * FROM it to check the result, then add the next.

You can name the columns in the header instead of aliasing them inside: WITH monthly (month, revenue) AS (SELECT ...).

CTEs in UPDATE and DELETE#

A WITH clause can come before UPDATE and DELETE statements too (or inside INSERT ... SELECT):

SQL

CREATE TABLE vip (customer VARCHAR(20) PRIMARY KEY);

INSERT INTO vip (customer)
WITH totals AS (SELECT customer, SUM(total) AS spent FROM orders GROUP BY customer)
SELECT customer FROM totals WHERE spent >= 390;

SELECT * FROM vip;

Output

+----------+
| customer |
+----------+
| Asha     |
| Rohan    |
+----------+

Recursive CTEs#

A recursive CTE refers to itself. It has two parts joined by UNION ALL:

An anchor member: the starting row(s).
A recursive member: a SELECT that uses the CTE's previous output to produce the next rows.

MySQL repeats step 2 until it produces no new rows. The simplest example counts from 1 to 5:

SQL

WITH RECURSIVE numbers (n) AS (
    SELECT 1                       -- anchor
    UNION ALL
    SELECT n + 1 FROM numbers      -- recursive member
    WHERE n < 5                    -- stop condition
)
SELECT n FROM numbers;

Output

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

Generating a date series

Reports often need a row for every day, even days with no sales. Generate the calendar, then LEFT JOIN the data onto it:

SQL

WITH RECURSIVE days AS (
    SELECT DATE('2026-02-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-02-07'
)
SELECT days.d, COALESCE(SUM(o.total), 0) AS revenue
FROM days
LEFT JOIN orders o ON o.ordered_on = days.d
GROUP BY days.d
ORDER BY days.d;

Output

+------------+---------+
| d          | revenue |
+------------+---------+
| 2026-02-01 |    0.00 |
| 2026-02-02 |    0.00 |
| 2026-02-03 |  260.00 |
| 2026-02-04 |    0.00 |
| 2026-02-05 |    0.00 |
| 2026-02-06 |    0.00 |
| 2026-02-07 |    0.00 |
+------------+---------+

Walking a hierarchy

This is the killer feature. A self join goes one level at a time, but a recursive CTE walks a tree of any depth:

SQL

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories (id)
);
INSERT INTO categories VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers',   1),
    (3, 'Laptops',     2),
    (4, 'Gaming laptops', 3),
    (5, 'Phones',      1),
    (6, 'Books',       NULL);

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 AS depth, CAST(name AS CHAR(200)) AS path
    FROM categories
    WHERE parent_id IS NULL                         -- anchor: the roots
    UNION ALL
    SELECT c.id, c.name, c.parent_id, t.depth + 1, CONCAT(t.path, ' > ', c.name)
    FROM categories c
    JOIN tree t ON c.parent_id = t.id               -- children of the previous level
)
SELECT CONCAT(REPEAT('  ', depth), name) AS category, depth, path
FROM tree
ORDER BY path;

Output

+----------------------+-------+----------------------------------------------------+
| category             | depth | path                                               |
+----------------------+-------+----------------------------------------------------+
| Books                |     0 | Books                                              |
| Electronics          |     0 | Electronics                                        |
|   Computers          |     1 | Electronics > Computers                            |
|     Laptops          |     2 | Electronics > Computers > Laptops                  |
|       Gaming laptops |     3 | Electronics > Computers > Laptops > Gaming laptops |
|   Phones             |     1 | Electronics > Phones                               |
+----------------------+-------+----------------------------------------------------+

Two details matter:

CAST(name AS CHAR(200)) in the anchor. The anchor fixes each column's type and length, so without the cast, path would be limited to the length of the longest root name and longer paths would fail with a "Data too long" error.
The recursive member joins categories to tree, which holds the rows found in the previous iteration.

To find everything under one category, change the anchor to WHERE id = 2. To find the ancestors of a category (its breadcrumb trail), reverse the join: start at the leaf and join c.id = t.parent_id.

Safety limits#

A recursive CTE without a working stop condition would loop forever. MySQL protects you:

SQL

WITH RECURSIVE forever AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM forever)
SELECT COUNT(*) FROM forever;

Output

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

Raise the limit for big series with SET SESSION cte_max_recursion_depth = 10000;. You can also add LIMIT inside the recursive CTE (MySQL 8.0.19+). For hierarchies with cycles (A → B → A), track the visited ids in a path string and stop when an id repeats.

CTE, derived table, view or temporary table?#

Tool	Lifetime	Reuse within the query	Best for
Derived table	one query	once	small one-off steps
CTE	one statement	many times; can be recursive	readable multi-step queries
View	permanent	any query	shared, reusable logic
Temporary table	session	many statements; can be indexed	heavy intermediate results

What's next#

CTEs organise queries. Window functions let you compute running totals, rankings and comparisons with the previous row, all without collapsing rows the way GROUP BY does.

Your first CTE#

Chaining several CTEs#

CTEs in UPDATE and DELETE#

Recursive CTEs#

Generating a date series

Walking a hierarchy

Safety limits#

CTE, derived table, view or temporary table?#

What's next#

Quick quiz