A recursive CTE walks hierarchical data that a single query could not otherwise reach. A category tree stores only each row’s immediate parent, so the depth is unknown until you follow the links. First, this tutorial builds the anchor and the recursive member that WITH RECURSIVE needs. Next, it adds a depth column and a readable path. Then it starts the walk halfway down to pull one subtree. Finally, it covers the guard that stops a runaway query, because a cycle in the data would otherwise loop forever.
Requirements for a recursive CTE:
- MySQL 8.0 or newer (tested on MySQL 8.4.10). MySQL 5.7 cannot do this without a stored procedure.
- Terminal access to the mysql client, and a user that can create a database.
- A self-referencing table — one with a parent_id pointing back at its own primary key. The setup below creates one.
How To Query Hierarchical Data With a Recursive CTE in MySQL.
The objective is a full category tree printed as an indented outline, from one flat table. Create the demo data first, so every query below returns exactly what the article shows.
CREATE DATABASE ndriel_tree_demo;
USE ndriel_tree_demo;
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
parent_id INT NULL,
CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES categories(id)
);
INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Store', NULL),
(2, 'Books', 1),
(3, 'Electronics', 1),
(4, 'Programming', 2),
(5, 'Databases', 2),
(6, 'PHP', 4),
(7, 'JavaScript', 4),
(8, 'MySQL', 5),
(9, 'Laptops', 3),
(10, 'Phones', 3),
(11, 'Frameworks', 6);The parent_id column points at another row in the same table, which is exactly what adding a foreign key in MySQL describes, pointed at itself. Store is the only row with no parent, so it is the root. Frameworks sits four levels below it.
Step 1.
First, write the two halves. The anchor selects the starting rows; the recursive member then joins the table back onto the block being defined.
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories AS c
JOIN tree AS t ON c.parent_id = t.id
)
SELECT id, name, parent_id, depth FROM tree ORDER BY depth, id;+------+-------------+-----------+-------+
| id | name | parent_id | depth |
+------+-------------+-----------+-------+
| 1 | Store | NULL | 0 |
| 2 | Books | 1 | 1 |
| 3 | Electronics | 1 | 1 |
| 4 | Programming | 2 | 2 |
| 5 | Databases | 2 | 2 |
| 9 | Laptops | 3 | 2 |
| 10 | Phones | 3 | 2 |
| 6 | PHP | 4 | 3 |
| 7 | JavaScript | 4 | 3 |
| 8 | MySQL | 5 | 3 |
| 11 | Frameworks | 6 | 4 |
+------+-------------+-----------+-------+Three rules make this work. The word RECURSIVE is mandatory, UNION ALL separates the halves, and only the second half may name the block. MySQL runs the anchor once, then repeats the second half against whatever the previous pass produced, until a pass returns nothing.
Step 2.
Next, carry a path down the tree and indent by depth. A string built during the walk gives you both a readable breadcrumb and a correct sort order.
WITH RECURSIVE tree AS (
SELECT id, name, 0 AS depth, CAST(name AS CHAR(200)) AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)
FROM categories AS c
JOIN tree AS t ON c.parent_id = t.id
)
SELECT CONCAT(REPEAT(' ', depth), name) AS category, depth, path
FROM tree
ORDER BY path;The CAST(name AS CHAR(200)) in the anchor is not decoration. MySQL fixes each column’s type from the anchor row, so an uncast name would size the column at 40 characters and truncate every longer path. Sorting by that path is also what puts each child directly beneath its own parent, which plain ORDER BY depth cannot do.
Step 3.
Then, start the walk somewhere other than the root. Only the anchor changes; the recursive member stays exactly as it was.
WITH RECURSIVE subtree AS (
SELECT id, name, 0 AS depth
FROM categories
WHERE name = 'Books'
UNION ALL
SELECT c.id, c.name, s.depth + 1
FROM categories AS c
JOIN subtree AS s ON c.parent_id = s.id
)
SELECT CONCAT(REPEAT(' ', depth), name) AS category, depth
FROM subtree
ORDER BY depth, id;+------------------------+-------+
| category | depth |
+------------------------+-------+
| Books | 0 |
| Programming | 1 |
| Databases | 1 |
| PHP | 2 |
| JavaScript | 2 |
| MySQL | 2 |
| Frameworks | 3 |
+------------------------+-------+Seven rows come back instead of eleven, and the depth restarts at 0 for Books. Reverse the join condition to ON c.id = s.parent_id and the same query climbs upward instead, listing every ancestor of a row.
Step 4.
A recursive query with no stop condition does not run forever. MySQL counts the passes and gives up.
WITH RECURSIVE counter AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM counter
)
SELECT * FROM counter;ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.The cte_max_recursion_depth variable defaults to 1000, and it is a safety net rather than a setting to tune. Raising it hides the problem; a genuine cycle in the data would still never terminate. Because parent_id here is a foreign key onto the same table, a row could legally be made its own ancestor, so the risk is real.
Step 5.
Finally, put the stop condition where it belongs — inside the recursive member, so it prunes the next pass.
WITH RECURSIVE tree AS (
SELECT id, name, 0 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, t.depth + 1
FROM categories AS c
JOIN tree AS t ON c.parent_id = t.id
WHERE t.depth < 2
)
SELECT CONCAT(REPEAT(' ', depth), name) AS category, depth
FROM tree
ORDER BY depth, id;+---------------------+-------+
| category | depth |
+---------------------+-------+
| Store | 0 |
| Books | 1 |
| Electronics | 1 |
| Programming | 2 |
| Databases | 2 |
| Laptops | 2 |
| Phones | 2 |
+---------------------+-------+The walk stops three levels down, so PHP and Frameworks never appear. Note the difference from filtering outside the block: an outer WHERE depth < 3 would produce the same rows, however MySQL would still have walked the entire tree first.
Result of the recursive CTE.
The payoff is Step 2, where a flat table with one parent_id column prints as an outline. This is the real output from MySQL 8.4.10:
+----------------------------+-------+------------------------------------------------+
| category | depth | path |
+----------------------------+-------+------------------------------------------------+
| Store | 0 | Store |
| Books | 1 | Store > Books |
| Databases | 2 | Store > Books > Databases |
| MySQL | 3 | Store > Books > Databases > MySQL |
| Programming | 2 | Store > Books > Programming |
| JavaScript | 3 | Store > Books > Programming > JavaScript |
| PHP | 3 | Store > Books > Programming > PHP |
| Frameworks | 4 | Store > Books > Programming > PHP > Frameworks |
| Electronics | 1 | Store > Electronics |
| Laptops | 2 | Store > Electronics > Laptops |
| Phones | 2 | Store > Electronics > Phones |
+----------------------------+-------+------------------------------------------------+
11 rows in set (0.00 sec)
Notes on the recursive CTE:
- Cast every string the recursion grows. The anchor decides each column’s width, so a path built with
CONCATis silently truncated without aCASTin the first half. UNION ALLis the usual choice. PlainUNIONis allowed and removes duplicate rows each pass, which can mask a cycle rather than fix it.- A path column doubles as a cycle guard, provided the anchor carries the separator too. Start it as
CAST(CONCAT(' > ', name) AS CHAR(200))and addWHERE LOCATE(CONCAT(' > ', c.name), t.path) = 0to the recursive member; each node is then visited once per branch. Without that leading separator the starting row alone escapes the check, because nothing precedes it in the path. - Index the parent_id column on a large tree. Each pass joins on it, so it is read once per level — confirm with an index read through EXPLAIN.
- The non-recursive form covered in writing a common table expression with WITH shares this syntax exactly. Only the self-reference and the
RECURSIVEkeyword differ. - A recursive block can also generate rows from nothing, such as a date series for a report. The anchor supplies the first value and the stop condition supplies the last.
