MySQL – Common Table Expressions (WITH)

A common table expression names a query so the rest of the statement can use it by that name. It was added in MySQL 8.0, and it is the single biggest improvement to the readability of complicated SQL — a nested query has to be read inside-out, a CTE reads top-down.

The shape

WITH completed AS (
    SELECT id, order_type, total FROM customer_order WHERE status = 'COMPLETED'
)
SELECT order_type, COUNT(*) AS orders, ROUND(SUM(total), 2) AS revenue
FROM   completed GROUP BY order_type ORDER BY order_type;

+------------+--------+---------+
| order_type | orders | revenue |
+------------+--------+---------+
| CARRYOUT   |      5 |   96.49 |
| DELIVERY   |      8 |  260.91 |
+------------+--------+---------+

WITH name AS ( … ), then a query that treats name as a table. The CTE exists only for the duration of the statement.

CTE, derived table or view?

A CTE and a derived table do the same work; the CTE is named, appears before the query that uses it, and can be referenced twice. Use a view when the definition is worth keeping for other queries too — a CTE when it matters only here.

Chaining

Several CTEs, comma-separated, each able to see the ones before it. This is where the readability argument is won — each step gets a name and a line:

WITH item_revenue AS (
    SELECT product_name, SUM(line_total) AS revenue FROM order_item GROUP BY product_name
), ranked AS (
    SELECT product_name, revenue, RANK() OVER (ORDER BY revenue DESC) AS rnk FROM item_revenue
)
SELECT rnk, product_name, ROUND(revenue, 2) AS revenue FROM ranked WHERE rnk <= 4 ORDER BY rnk;

+-----+---------------------+---------+
| rnk | product_name        | revenue |
+-----+---------------------+---------+
|   1 | Pepperoni Pizza     |   98.69 |
|   2 | Meat Lovers Pizza   |   64.47 |
|   3 | Supreme Pizza       |   55.72 |
|   4 | BBQ Chicken Pizza   |   36.98 |
|   4 | Veggie Lovers Pizza |   36.98 |
+-----+---------------------+---------+

Two products tie on revenue and both get rank 4 — see window functions for what RANK does next. Note also that this filters on rnk, which a plain query cannot do: window functions are not allowed in WHERE, so wrapping the ranking in a CTE and filtering outside is the standard way round it.

RECURSIVE

A recursive CTE refers to itself. It has two halves joined by UNION ALL: an anchor that produces the starting rows, and a recursive step that produces more from the rows already generated, until it produces none.

Generating a series

WITH RECURSIVE days AS (
    SELECT DATE('2024-06-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2024-06-05'
)
SELECT d FROM days;

+------------+
| d          |
+------------+
| 2024-06-01 |
| 2024-06-02 |
| 2024-06-03 |
| 2024-06-04 |
| 2024-06-05 |
+------------+

This solves a problem every reporting query eventually hits: a day with no orders produces no row, so a chart drawn from GROUP BY date silently skips it and the line joins across the gap. Generate the dates, then LEFT JOIN the totals onto them and the empty days appear as zeros.

Walking a tree

CREATE TABLE staff (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(80) NOT NULL,
    role       VARCHAR(40) NOT NULL,
    manager_id BIGINT      NULL,
    CONSTRAINT fk_staff_manager FOREIGN KEY (manager_id) REFERENCES staff (id)
);

INSERT INTO staff (id, name, role, manager_id) VALUES
    (1, 'Dana Whitfield', 'General Manager', NULL),
    (2, 'Alex Rivera',    'Shift Lead',      1),
    (3, 'Priya Nair',     'Shift Lead',      1),
    (4, 'Sam Chen',       'Driver',          2),
    (5, 'Casey Lindgren', 'Cook',            3);

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM   staff WHERE manager_id IS NULL
    UNION ALL
    SELECT s.id, s.name, s.manager_id, c.depth + 1
    FROM   staff s JOIN chain c ON s.manager_id = c.id
)
SELECT depth, name FROM chain ORDER BY depth, name;

+-------+----------------+
| depth | name           |
+-------+----------------+
|     0 | Dana Whitfield |
|     1 | Alex Rivera    |
|     1 | Priya Nair     |
|     2 | Casey Lindgren |
|     2 | Sam Chen       |
+-------+----------------+

This is the query that a self join cannot write, because a self join walks exactly one level and you do not know the depth in advance. Before MySQL 8 the answers were a loop in application code, or a closure table — which is still the right answer when subtree reads have to be fast.

Two things that will bite

Runaway recursion. A recursive step whose termination condition never fires runs until it hits cte_max_recursion_depth, which defaults to 1000, and then errors. That limit is a safety net, not a target: if you legitimately need more, raise it for the session, and if you did not, the error just saved you. Cycles in the data — a manager who reports to their own subordinate — will do this, and are worth guarding against by tracking the path.

UNION ALL versus UNION. UNION deduplicates each round, which is occasionally the thing that stops a cycle and is usually just slower. Use UNION ALL unless you know why you want the other.

Performance

A CTE is not automatically materialised in MySQL 8 — the optimizer may merge it into the outer query exactly as it would a derived table, so writing one is usually free. But a CTE referenced twice may be evaluated twice; if the CTE is expensive and reused, check with EXPLAIN rather than assuming.

Unlike some databases, MySQL has no hint to force materialisation. If it matters, a temporary table is the explicit answer.

What to remember

• WITH name AS (…) — MySQL 8 and later only.
• Chain CTEs to give each step of a complicated query a name.
• A CTE can be referenced twice; a derived table cannot.
• Filter on a window function by computing it in a CTE and filtering outside.
• RECURSIVE for trees and generated series; mind the 1000-row default depth.