Round one is Meera. Round two joins employees whose manager_id is Meera's id: Arjun and Divya. Round three finds Kabir under Arjun; round four Leela under Kabir; round five finds no one under Leela and stops. The join is the stopping condition: leaves have no children.

To start lower — everyone under Arjun — change only the anchor: WHERE id = 2. To include only the subordinates and not Arjun himself, anchor on WHERE manager_id = 2 with depth 1.

Walking up: a person's chain of command

Reverse the join to climb from one employee to the top:

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS steps FROM employees WHERE id = 5
  UNION ALL
  SELECT m.id, m.name, m.manager_id, chain.steps + 1
  FROM employees m
  JOIN chain ON m.id = chain.manager_id
)
SELECT name, steps FROM chain ORDER BY steps;

This returns Leela, Kabir, Arjun, Meera — and stops when Meera's manager_id is NULL, since m.id = NULL matches nothing.

Building a path

To show where a node sits — Electronics > Phones > Android — carry a string column and extend it each round:

WITH RECURSIVE tree AS (
  SELECT id, name, CAST(name AS CHAR(200)) AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, CONCAT(t.path, ' > ', c.name)
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT path FROM tree ORDER BY path;

The CAST matters in MySQL. The anchor decides each column's type: without it, path would be typed as wide as the longest root name, and a longer path built later is truncated (or rejected with "Data too long for column" in strict mode). Casting to CHAR(200) — or any width that fits the deepest path — avoids it. PostgreSQL has a similar rule and wants the types of both members to match exactly. Sorting by the path puts every child right after its parent, which displays a tree as an indented list.

Generating series

A recursive CTE with no table at all is a generator. Numbers:

WITH RECURSIVE seq AS (
  SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100
)
SELECT n FROM seq;

Dates — the standard fix for reports with missing days:

WITH RECURSIVE days AS (
  SELECT '2024-03-01' AS day
  UNION ALL
  SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM days WHERE day < '2024-03-07'
)
SELECT d.day, COALESCE(SUM(s.amount), 0) AS revenue
FROM days d
LEFT JOIN sales s ON s.sale_date = d.day
GROUP BY d.day
ORDER BY d.day;

A plain GROUP BY sale_date over sales skips a day with no sales; the date series provides every day, and the LEFT JOIN with COALESCE turns an empty day into 0. PostgreSQL has generate_series() for this; MySQL has no such function, so the recursive CTE is the idiom. Bound the series by reading the range from the data — SELECT MIN(sale_date) … in the anchor, WHERE day < (SELECT MAX(sale_date) FROM sales) in the recursive member.

Keeping recursion bounded

A recursive member that never runs out of rows would run forever. MySQL protects you with cte_max_recursion_depth (default 1000 rounds) and max_execution_time, but the query should stop on its own:

• Counters stop at a limit: WHERE n < 100.
• Joins down a tree stop at the leaves — but only if the data really is a tree. A cycle (A manages B, B manages A, by a data-entry mistake) loops forever.
• Guard against cycles by carrying a depth and stopping at a sane maximum (WHERE org.depth < 20), or by carrying the path of ids and refusing to revisit: WHERE FIND_IN_SET(e.id, org.id_path) = 0. PostgreSQL 14 adds a CYCLE clause for this.
• Use UNION instead of UNION ALL when rows can be regenerated — such as walking a graph that has several routes to one node — so already-seen rows are dropped.

Recursion for aggregates

Because aggregates are not allowed inside, total-over-a-subtree questions — "how many people work under each manager, at any depth" — are answered in two steps: the recursive CTE produces one row per (manager, subordinate) pair, and the outer query groups:

WITH RECURSIVE reports AS (
  SELECT manager_id AS boss, id AS person FROM employees WHERE manager_id IS NOT NULL
  UNION ALL
  SELECT r.boss, e.id
  FROM reports r
  JOIN employees e ON e.manager_id = r.person
)
SELECT boss, COUNT(*) AS team_size FROM reports GROUP BY boss ORDER BY boss;

The anchor pairs each manager with each direct report; each round extends every pair one level further down. Grouping by boss counts all of them.

In other databases

PostgreSQL and SQLite use WITH RECURSIVE exactly as MySQL does. SQL Server writes plain WITH and limits depth with OPTION (MAXRECURSION n) (default 100). Oracle accepts recursive WITH and also has its older CONNECT BY PRIOR syntax, which you may still meet in legacy code.

Pitfalls

• Forgetting RECURSIVE, or the stopping condition.
• An anchor column too narrow for the values built in later rounds.
• Cycles in data that "should" be a tree.
• Aggregating inside the recursive member instead of in the final query.
• Relying on the CTE's row order instead of an outer ORDER BY.

Key takeaways

• WITH RECURSIVE = anchor UNION ALL recursive member; each round reads only the previous round's rows; recursion stops at an empty round.
• Join on child.parent_id = cte.id to walk down a tree, the other way round to walk up.
• Carry a depth column for levels and a CAST(… AS CHAR(n)) path column for readable paths.
• Generate number and date series to fill gaps before a LEFT JOIN.
• Keep recursion bounded: counters, depth limits and cycle checks; aggregate outside the CTE.