Naming the steps
Complex questions are easier in steps: first work out revenue per customer, then keep the top ones, then add their city. With subqueries you nest those steps inside each other, and the SQL reads from the inside out. A common table expression (CTE) lets you write the steps in order, each with a name. It needs MySQL 8.0 or newer.
WITH customer_revenue AS (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id
)
SELECT c.first_name, c.city, cr.revenue
FROM customer_revenue cr
JOIN customers c ON c.id = cr.customer_id
WHERE cr.revenue > 300
ORDER BY cr.revenue DESC;+------------+----------+---------+
| first_name | city | revenue |
+------------+----------+---------+
| Asha | Delhi | 502.58 |
| Jack | New York | 351.47 |
+------------+----------+---------+The WITH name AS (query) block defines customer_revenue. The main SELECT after it uses that name like a table. The CTE exists only for this one statement.
Several CTEs in a row
Separate CTEs with commas. Later ones can use earlier ones:
WITH order_totals AS (
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id
),
stats AS (
SELECT AVG(total) AS average_total FROM order_totals
)
SELECT ot.order_id, ot.total, ROUND(s.average_total, 2) AS average_total
FROM order_totals ot
CROSS JOIN stats s
WHERE ot.total > s.average_total
ORDER BY ot.total DESC;+----------+--------+---------------+
| order_id | total | average_total |
+----------+--------+---------------+
| 12 | 249.97 | 102.49 |
| 20 | 218.74 | 102.49 |
| 4 | 199.99 | 102.49 |
| 10 | 128.50 | 102.49 |
| 11 | 109.45 | 102.49 |
| 5 | 102.99 | 102.49 |
+----------+--------+---------------+The same logic with nested subqueries is possible, but the CTE version names the pieces and reads from top to bottom. A CTE can also be used more than once in the main query, which is awkward with a derived table.
CTEs with window functions
A CTE is the natural place to compute a window function so the main query can filter on it. The cheapest product in each category:
WITH ranked AS (
SELECT category_id, name, price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price) AS rn
FROM products
)
SELECT category_id, name, price FROM ranked WHERE rn = 1 ORDER BY category_id;+-------------+---------------------+-------+
| category_id | name | price |
+-------------+---------------------+-------+
| 1 | Wireless Mouse | 24.99 |
| 2 | SQL Basics Handbook | 29.95 |
| 3 | Ceramic Mug Set | 18.75 |
| 4 | Water Bottle | 12.00 |
| 5 | Puzzle 1000 Pieces | 19.99 |
+-------------+---------------------+-------+Recursive CTEs
Some data refers to itself: employees report to employees, categories contain subcategories. A recursive CTE repeats a step until no more rows appear. It has two parts joined by UNION ALL: an anchor that returns the starting rows, and a recursive part that refers to the CTE's own name.
The smallest example generates numbers:
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n, n * n AS square FROM numbers;+------+--------+
| n | square |
+------+--------+
| 1 | 1 |
| 2 | 4 |
| 3 | 9 |
| 4 | 16 |
| 5 | 25 |
+------+--------+The anchor returns 1. The recursive part takes the previous row (n) and produces n + 1, until the WHERE stops it. Without that condition, it would never end, which is why MySQL has a safety limit:
SELECT @@cte_max_recursion_depth AS maximum_depth;+---------------+
| maximum_depth |
+---------------+
| 1000 |
+---------------+Walking an organisation chart
Here is the real use. Starting at the chief executive, descend through the reports, keeping track of the level and the path:
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 0 AS level, CAST(name AS CHAR(300)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, org.level + 1, CONCAT(org.path, ' > ', e.name)
FROM employees e
JOIN org ON e.manager_id = org.id
)
SELECT CONCAT(REPEAT(' ', level), name) AS hierarchy, level, path
FROM org
ORDER BY path;+-----------------------+-------+----------------------------------------------------+
| hierarchy | level | path |
+-----------------------+-------+----------------------------------------------------+
| Meera Iyer | 0 | Meera Iyer |
| Nina Petrov | 1 | Meera Iyer > Nina Petrov |
| Omar Haddad | 2 | Meera Iyer > Nina Petrov > Omar Haddad |
| Uma Reddy | 3 | Meera Iyer > Nina Petrov > Omar Haddad > Uma Reddy |
| Paula Gomez | 2 | Meera Iyer > Nina Petrov > Paula Gomez |
| Sam Wright | 2 | Meera Iyer > Nina Petrov > Sam Wright |
| Tara Singh | 1 | Meera Iyer > Tara Singh |
| Tom Hughes | 1 | Meera Iyer > Tom Hughes |
| Quinn Baker | 2 | Meera Iyer > Tom Hughes > Quinn Baker |
| Rina Das | 2 | Meera Iyer > Tom Hughes > Rina Das |
| Victor Lang | 1 | Meera Iyer > Victor Lang |
+-----------------------+-------+----------------------------------------------------+Each pass finds the employees whose manager was found in the previous pass. This works for any depth, which is what a fixed number of self joins could not do. The CAST fixes the column width of path from the anchor, so longer paths fit.
You can also go upwards. The chain of managers above Uma:
WITH RECURSIVE chain AS (
SELECT id, name, manager_id FROM employees WHERE name = 'Uma Reddy'
UNION ALL
SELECT e.id, e.name, e.manager_id FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT name FROM chain;+-------------+
| name |
+-------------+
| Uma Reddy |
| Omar Haddad |
| Nina Petrov |
| Meera Iyer |
+-------------+A date series
Reports often need a row for every day, even days with no data. A recursive CTE can generate them, which you then LEFT JOIN to your data:
WITH RECURSIVE days AS (
SELECT DATE('2024-07-15') AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2024-07-21'
)
SELECT days.d, COUNT(o.id) AS orders
FROM days
LEFT JOIN orders o ON o.order_date = days.d
GROUP BY days.d
ORDER BY days.d;+------------+--------+
| d | orders |
+------------+--------+
| 2024-07-15 | 0 |
| 2024-07-16 | 0 |
| 2024-07-17 | 0 |
| 2024-07-18 | 0 |
| 2024-07-19 | 1 |
| 2024-07-20 | 0 |
| 2024-07-21 | 0 |
+------------+--------+CTEs and performance
In MySQL 8, a CTE is usually merged into the outer query or computed once and stored temporarily, depending on how it is used. It is not automatically faster than the equivalent subquery. Choose CTEs for clarity, and use EXPLAIN when speed matters. A recursive CTE that walks a very deep or wide tree can become slow, so keep the number of rows under control.
Common mistakes
- Forgetting
RECURSIVEin MySQL for a recursive CTE. - A recursive part with no stopping condition, giving "Recursive query aborted after 1001 iterations".
- Using
UNIONinstead ofUNION ALLin the recursive part. - Letting the anchor column type be too narrow, so longer values from later passes are cut off.
- Assuming a CTE is a materialised table that you can index.
Exercise
Write a recursive CTE that lists the numbers 10, 20, 30 and so on up to 100. Then, using a CTE, list each category with the total stock of its products, keeping only categories with more than 400 units.
Show one possible answer
WITH RECURSIVE tens AS (
SELECT 10 AS n
UNION ALL
SELECT n + 10 FROM tens WHERE n < 100
)
SELECT GROUP_CONCAT(n ORDER BY n) AS multiples_of_ten FROM tens;
WITH category_stock AS (
SELECT category_id, SUM(stock) AS units FROM products GROUP BY category_id
)
SELECT c.name, cs.units
FROM category_stock cs
JOIN categories c ON c.id = cs.category_id
WHERE cs.units > 400
ORDER BY cs.units DESC;+--------------------------------+
| multiples_of_ten |
+--------------------------------+
| 10,20,30,40,50,60,70,80,90,100 |
+--------------------------------+
+--------+-------+
| name | units |
+--------+-------+
| Sports | 660 |
| Books | 410 |
+--------+-------+Key points
- A CTE (WITH name AS (SELECT ...)) is a named temporary result you can use in the main query, like a view that lasts for one statement. Requires MySQL 8.0+.
- Several CTEs can be chained, and a later one can refer to an earlier one. This turns a nest of subqueries into readable steps.
- A recursive CTE has an anchor query and a recursive part joined by UNION ALL; it repeats until no new rows appear.
- Recursive CTEs read organisation charts, category trees and date or number sequences. A depth limit (cte_max_recursion_depth) stops runaway recursion.