#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

Recursive CTEs in MySQL: Hierarchies and Sequences

Some questions have no fixed depth. "List everyone who reports to the principal, directly or indirectly" might mean two levels in one college and six in another. "Show every date in November, even days with no sales" needs rows that are not stored anywhere. A recursive CTE handles both: it starts from a few rows and keeps feeding its own output back into itself until no new rows appear. MySQL supports it from version 8.0 with WITH RECURSIVE.

The two parts of every recursive CTE

WITH RECURSIVE nums AS (
    SELECT 1 AS n                 -- 1. anchor member: runs once
    UNION ALL
    SELECT n + 1 FROM nums        -- 2. recursive member: refers to nums
    WHERE n < 5                   --    stop condition
)
SELECT n FROM nums;
n
1
2
3
4
5

How MySQL evaluates it:

IterationInput rows (from previous step)New rows produced
Anchornone1
112
223
334
445
55none (5 < 5 is false), so recursion stops

Each iteration sees only the rows produced by the previous iteration, not the whole result so far. The final result is all rows from every step combined.

Example 1: a reporting hierarchy with levels and paths

CREATE TABLE staff (
  id INT PRIMARY KEY, name VARCHAR(20), role VARCHAR(25), manager_id INT
);
INSERT INTO staff VALUES
 (1,'Ramesh','Principal',NULL),
 (2,'Sneha','HOD Computer Science',1),
 (3,'Imran','HOD Electronics',1),
 (4,'Tanvi','Assistant Professor',2),
 (5,'Gopal','Lab Assistant',4),
 (6,'Ritu','Assistant Professor',3);

WITH RECURSIVE org AS (
    SELECT id, name, role, 1 AS level,
           CAST(name AS CHAR(200)) AS path
    FROM staff
    WHERE manager_id IS NULL                       -- start at the top
    UNION ALL
    SELECT s.id, s.name, s.role, o.level + 1,
           CONCAT(o.path, ' > ', s.name)
    FROM staff s
    JOIN org o ON s.manager_id = o.id              -- children of rows found so far
)
SELECT name, role, level, path FROM org ORDER BY path;
namerolelevelpath
RameshPrincipal1Ramesh
ImranHOD Electronics2Ramesh > Imran
RituAssistant Professor3Ramesh > Imran > Ritu
SnehaHOD Computer Science2Ramesh > Sneha
TanviAssistant Professor3Ramesh > Sneha > Tanvi
GopalLab Assistant4Ramesh > Sneha > Tanvi > Gopal

Sorting by path puts each person under their manager, which is handy for an indented org chart in a report. To list only one branch, change the anchor, for example WHERE name = 'Sneha'.

Why the CAST is there

Without CAST(name AS CHAR(200)), the query fails:

ERROR 1406 (22001): Data too long for column 'path' at row 1

MySQL fixes each column's type from the anchor member. The anchor's name is VARCHAR(20), so path becomes 20 characters wide and the longer concatenated paths do not fit. Always widen text columns that grow during recursion.

Example 2: walking up instead of down

Who is in Gopal's chain of command? Reverse the join: follow manager_id upwards.

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id FROM staff WHERE name = 'Gopal'
    UNION ALL
    SELECT s.id, s.name, s.manager_id
    FROM staff s
    JOIN chain c ON s.id = c.manager_id
)
SELECT name FROM chain;
name
Gopal
Tanvi
Sneha
Ramesh

The same pattern gives breadcrumbs for nested product categories (Electronics > Mobiles > Accessories) or threaded comment replies.

Example 3: generating a date series

WITH RECURSIVE days AS (
    SELECT DATE('2026-11-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-11-07'
)
SELECT d, DAYNAME(d) AS day_name FROM days;
dday_name
2026-11-01Sunday
2026-11-02Monday
2026-11-03Tuesday
2026-11-04Wednesday
2026-11-05Thursday
2026-11-06Friday
2026-11-07Saturday

LEFT JOIN your sales table onto this list and days without sales show up as zero instead of disappearing. A full worked report using this trick is in CROSS JOIN in MySQL.

Limits and safety nets

If the stop condition is missing or wrong, MySQL does not loop forever:

WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums)
SELECT COUNT(*) FROM nums;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
Try increasing @@cte_max_recursion_depth to a larger value.

The default cte_max_recursion_depth is 1000. When you genuinely need more (a year of dates is 365, but a list of 50,000 numbers is not), raise it for your session only: SET SESSION cte_max_recursion_depth = 100000;. The max_execution_time setting (milliseconds, applies to SELECT) is a second safety net.

Common mistakes

Recursive CTEs build on ordinary ones, so if WITH itself is new, start with common table expressions in MySQL. For one-level parent lookups, a self join is simpler, and the list of what else MySQL 8 added is in MySQL 8 new features.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →