MySQL Hierarchical Data: Category Trees with Recursive CTEs

Categories inside categories, comments with replies, folders, organization charts: they are all trees. A relational table stores a tree in a few well-known ways. Each is good at different questions.

The adjacency list: a parent_id column

Each row stores a reference to its parent. This is the simplest model, and it is the one to start with (Self-Referencing Tables):

query.sql

SQL

CREATE TABLE categories (
    id INT PRIMARY KEY,
    parent_id INT NULL,
    name VARCHAR(30) NOT NULL,
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE RESTRICT
);

INSERT INTO categories (id, parent_id, name) VALUES
(1, NULL, 'Electronics'),
(2, 1, 'Computers'),
(3, 1, 'Phones'),
(4, 2, 'Laptops'),
(5, 2, 'Desktops'),
(6, 4, 'Gaming laptops'),
(7, 3, 'Smartphones'),
(8, NULL, 'Books');

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

A root has parent_id = NULL. The tree:

Text

PLAIN

Electronics
  Computers
    Laptops
      Gaming laptops
    Desktops
  Phones
    Smartphones
Books

Simple questions are easy: the children of Computers, or the parent of Laptops, are one WHERE and one join. The hard questions are “all descendants” and “all ancestors”, because the depth is unknown. A recursive CTE answers them (Recursive CTEs; a shorter introduction to the idea is in Recursive Hierarchies). This lesson goes further and compares the ways to store a tree.

All descendants of a category

query.sql

SQL

WITH RECURSIVE subtree AS (
    SELECT id, parent_id, name, 0 AS depth FROM categories WHERE id = 2
    UNION ALL
    SELECT c.id, c.parent_id, c.name, s.depth + 1
    FROM categories c JOIN subtree s ON c.parent_id = s.id
)
SELECT CONCAT(REPEAT('  ', depth), name) AS tree, depth FROM subtree ORDER BY depth, name;

Output

OUTPUT

+--------------------+-------+
| tree               | depth |
+--------------------+-------+
| Computers          |     0 |
|   Desktops         |     1 |
|   Laptops          |     1 |
|     Gaming laptops |     2 |
+--------------------+-------+
4 rows in set

The first part of the CTE is the anchor (the starting row). The second part is the recursive step that finds the children of the rows found so far, until no more are found.

The whole tree, in display order

query.sql

SQL

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 c JOIN tree t ON c.parent_id = t.id
)
SELECT CONCAT(REPEAT('  ', depth), name) AS tree, path FROM tree ORDER BY path;

Output

OUTPUT

+----------------------+----------------------------------------------------+
| tree                 | path                                               |
+----------------------+----------------------------------------------------+
| Books                | Books                                              |
| Electronics          | Electronics                                        |
|   Computers          | Electronics > Computers                            |
|     Desktops         | Electronics > Computers > Desktops                 |
|     Laptops          | Electronics > Computers > Laptops                  |
|       Gaming laptops | Electronics > Computers > Laptops > Gaming laptops |
|   Phones             | Electronics > Phones                               |
|     Smartphones      | Electronics > Phones > Smartphones                 |
+----------------------+----------------------------------------------------+
8 rows in set

Sorting by the path puts every child right under its parent. The CAST(... AS CHAR(200)) gives the column a wide enough type: without it, the path column takes the width of the first row’s name, and longer paths would be cut or fail.

Walk up from a category to the root:

query.sql

SQL

WITH RECURSIVE up AS (
    SELECT id, parent_id, name, 0 AS steps FROM categories WHERE id = 6
    UNION ALL
    SELECT c.id, c.parent_id, c.name, u.steps + 1 FROM categories c JOIN up u ON c.id = u.parent_id
)
SELECT GROUP_CONCAT(name ORDER BY steps DESC SEPARATOR ' > ') AS breadcrumb FROM up;

Output

OUTPUT

+----------------------------------------------------+
| breadcrumb                                         |
+----------------------------------------------------+
| Electronics > Computers > Laptops > Gaming laptops |
+----------------------------------------------------+
1 row in set

Products in a category and all its subcategories

query.sql

SQL

CREATE TABLE products (id INT PRIMARY KEY, category_id INT NOT NULL, name VARCHAR(30) NOT NULL, FOREIGN KEY (category_id) REFERENCES categories (id));
INSERT INTO products VALUES (1, 6, 'Gaming Laptop X'), (2, 4, 'Office Laptop'), (3, 5, 'Tower PC'), (4, 7, 'Phone Pro'), (5, 8, 'SQL Handbook');

WITH RECURSIVE subtree AS (
    SELECT id FROM categories WHERE id = 2
    UNION ALL
    SELECT c.id FROM categories c JOIN subtree s ON c.parent_id = s.id
)
SELECT p.name FROM products p JOIN subtree s ON s.id = p.category_id ORDER BY p.name;

Output

OUTPUT

+-----------------+
| name            |
+-----------------+
| Gaming Laptop X |
| Office Laptop   |
| Tower PC        |
+-----------------+
3 rows in set

Cycles

A bug or a bad update can create a loop: A is the parent of B, and B is the parent of A. A recursive CTE would then run forever. MySQL protects you with a limit:

query.sql

SQL

UPDATE categories SET parent_id = 6 WHERE id = 1;       -- Electronics now has "Gaming laptops" as its parent: a loop

WITH RECURSIVE walk AS (
    SELECT id, parent_id, 1 AS n FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id, w.n + 1 FROM categories c JOIN walk w ON c.id = w.parent_id
)
SELECT COUNT(*) FROM walk;

Output

OUTPUT

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

The error comes from cte_max_recursion_depth (1000 by default). A better protection is to detect a repeated id inside the query by keeping the visited path, or to refuse a parent change that would create a loop in your application code. Restore the data:

query.sql

SQL

1
2

UPDATE categories SET parent_id = NULL WHERE id = 1;
SELECT id, parent_id, name FROM categories WHERE id = 1;

Output

OUTPUT

+----+-----------+-------------+
| id | parent_id | name        |
+----+-----------+-------------+
|  1 |      NULL | Electronics |
+----+-----------+-------------+
1 row in set

Deleting a node

With ON DELETE RESTRICT, deleting a category that has children fails: you must decide what happens to the children first (move them, or delete them from the bottom up). ON DELETE CASCADE on a self-reference deletes the whole subtree with one statement, which can be convenient and dangerous (Foreign Key Actions).

The other models

Model	How it stores the tree	Strong at	Weak at
Adjacency list (above)	`parent_id`	Simple, easy to move a node, MySQL 8 CTEs make queries easy	Deep subtree queries do repeated lookups
Materialized path	A string such as `/1/2/4/` in each row	Subtree with `WHERE path LIKE '/1/2/%'` (can use an index), breadcrumbs by splitting	Moving a subtree rewrites many paths
Closure table	A separate table with a row for every ancestor and descendant pair	Fast subtree and ancestor queries with plain joins	More rows, more work on every insert and move
Nested sets	`lft` and `rgt` numbers	Very fast reads	Painful writes. Rarely worth it today

A materialized path in a few lines:

query.sql

SQL

CREATE TABLE folders (id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, path VARCHAR(200) NOT NULL, KEY idx_path (path));
INSERT INTO folders VALUES (1, 'docs', '/1/'), (2, 'reports', '/1/2/'), (3, '2026', '/1/2/3/'), (4, 'images', '/1/4/'), (5, 'music', '/5/');

SELECT name FROM folders WHERE path LIKE '/1/2/%' ORDER BY path;

Output

OUTPUT

+---------+
| name    |
+---------+
| reports |
| 2026    |
+---------+
2 rows in set

A closure table for the same idea: one row per (ancestor, descendant, distance):

query.sql

SQL

CREATE TABLE folder_paths (ancestor INT NOT NULL, descendant INT NOT NULL, depth INT NOT NULL, PRIMARY KEY (ancestor, descendant), KEY idx_desc (descendant));
INSERT INTO folder_paths VALUES (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0),(1,2,1),(1,3,2),(1,4,1),(2,3,1);

SELECT f.name, p.depth FROM folder_paths p JOIN folders f ON f.id = p.descendant WHERE p.ancestor = 2 AND p.depth > 0 ORDER BY p.depth, f.name;
SELECT f.name FROM folder_paths p JOIN folders f ON f.id = p.ancestor WHERE p.descendant = 3 ORDER BY p.depth DESC;

Output

OUTPUT

+------+-------+
| name | depth |
+------+-------+
| 2026 |     1 |
+------+-------+
1 row in set

+---------+
| name    |
+---------+
| docs    |
| reports |
| 2026    |
+---------+
3 rows in set

Which to choose

A tree with a few levels and not too many nodes (categories, menus, comments): the adjacency list with recursive CTEs. It is the simplest and fully supported in MySQL 8.
A tree that is read far more than written, and where subtree queries must be very fast: closure table or materialized path.
Do not use nested sets for new work.

lightbulb

Limit the depth in the product

Many “tree” features work better with a small, enforced depth (for example, categories at most 3 levels). It keeps queries, screens and users simple.

The adjacency list: a parent_id column

All descendants of a category

The whole tree, in display order

Ancestors: the breadcrumb

Products in a category and all its subcategories

Cycles

Deleting a node

The other models

Which to choose

Limit the depth in the product