Recursive CTE in MySQL

Anchor + Recursive Member + Stop Rule

A recursive CTE contains a nonrecursive anchor followed by a recursive member, normally joined with UNION ALL. WITH RECURSIVE is required when a CTE refers to itself.

WITH RECURSIVE sequence_cte (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1
  FROM sequence_cte
  WHERE n < 5
)
SELECT n FROM sequence_cte;

Verified Number Series 1 to 5

The anchor emits 1. Each iteration adds one. When the previous n reaches 5, the condition n < 5 is false, so no next row is produced and recursion ends.

Generate five dates

WITH RECURSIVE dates (day_value) AS (
  SELECT DATE('2026-08-14')
  UNION ALL
  SELECT day_value + INTERVAL 1 DAY
  FROM dates
  WHERE day_value < DATE('2026-08-18')
)
SELECT day_value FROM dates;

Verified School Hierarchy Dataset

CREATE TABLE org_employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(60) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
  FOREIGN KEY (manager_id)
    REFERENCES org_employees(employee_id)
);

INSERT INTO org_employees VALUES
(1, 'Principal', NULL),
(2, 'Coordinator', 1),
(3, 'Teacher A', 2),
(4, 'Teacher B', 2),
(5, 'Lab Assistant', 3);

The root has manager_id NULL. Coordinator reports to Principal; two teachers report to Coordinator; Lab Assistant reports to Teacher A.

Traverse the Hierarchy with Depth and Path

WITH RECURSIVE org AS (
  SELECT employee_id, employee_name, manager_id,
         0 AS depth,
         CAST(employee_name AS CHAR(300)) AS path
  FROM org_employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.employee_name, e.manager_id,
         o.depth + 1,
         CONCAT(o.path, ' > ', e.employee_name)
  FROM org AS o
  JOIN org_employees AS e
    ON e.manager_id = o.employee_id
  WHERE o.depth < 10
)
SELECT employee_id, employee_name, depth, path
FROM org
ORDER BY path;

CAST in the anchor defines enough path width. The depth guard is defensive; the natural recursion also stops at leaves with no children.

Termination, Depth Limits and Cycle Detection

MySQL's cte_max_recursion_depth protects the server; its documented default is 1000. It is not a replacement for a correct stop predicate. During development, also use a reasonable depth condition and execution timeout.

WITH RECURSIVE org AS (
  SELECT employee_id, manager_id,
         CAST(employee_id AS CHAR(300)) AS id_path,
         0 AS depth
  FROM org_employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id,
         CONCAT(o.id_path, ',', e.employee_id),
         o.depth + 1
  FROM org AS o
  JOIN org_employees AS e
    ON e.manager_id = o.employee_id
  WHERE o.depth < 20
    AND FIND_IN_SET(e.employee_id, o.id_path) = 0
)
SELECT * FROM org;

The id_path guard rejects a node already visited on that path. Prevent cycles at data-validation time too; defensive queries should not normalize corrupt hierarchy data.

Type Inference and Recursive-Member Restrictions

• Result column types are inferred from the nonrecursive anchor, not the recursive member.
• Widen growing strings with CAST in the anchor.
• The recursive member must reference the CTE once and in its FROM clause.
• MySQL restricts aggregate/window functions, GROUP BY, ORDER BY and DISTINCT inside the recursive SELECT member.
• UNION DISTINCT between anchor and recursive members can remove duplicate rows, but it is not a substitute for a designed cycle rule.

Performance, EXPLAIN and Practice

• Index hierarchy links such as manager_id.
• Keep projected recursive rows narrow.
• Cap depth according to the domain.
• Use EXPLAIN; recursive cost estimates are per iteration and total iterations are data-dependent.
• Test roots, leaves, multiple roots, orphaned rows and cycles.

Practice: generate 1–10; generate a seven-day calendar; return only descendants of Coordinator; add a cycle in a disposable copy and confirm the guard; calculate maximum observed depth outside the recursive member.

Official References

• MySQL 8.4: Recursive CTEs
• MySQL 8.4: cte_max_recursion_depth
• MySQL 8.4: EXPLAIN Statement

Syntax and behavior were checked against the official MySQL 8.4 manual. Verify plans and limits on your own server before production use.