Recursive CTEs for Hierarchies & Trees0%

Recursive CTEs for Hierarchies & Trees

Intermediate14 min read•Updated: 2026-09-12

Recursive CTEs for Hierarchies & Trees

Standard relational tables store flat sets of rows, yet real-world enterprise data is filled with hierarchies, trees, and graphs:

Organizational reporting charts (CEOs -> VPs -> Managers -> Developers)
Multi-level product categories (Electronics -> Computers -> Laptops -> Gaming Laptops)
Bill of Materials (BOM) in manufacturing (Car -> Engine -> Cylinder -> Piston Rings)

Before MySQL 8.0, querying arbitrarily deep hierarchies required recursive application code or complex stored procedures. Recursive Common Table Expressions (WITH RECURSIVE) solve this problem natively within pure SQL.

Structure of a Recursive CTE

A recursive CTE consists of three indispensable components:

1
The Anchor Member: An initial non-recursive query that generates the starting base rows (e.g., the top-level CEO or root category).
2
UNION ALL (or UNION): Joins the anchor results to the subsequent recursive iterations.
3
The Recursive Member: A query that references the CTE itself, joining to the base table to fetch the next level of children. It continues executing until it yields an empty set.

1WITH RECURSIVE cte_name AS (
2    -- 1. Anchor Member
3    SELECT id, name, parent_id, 1 AS depth
4    FROM category
5    WHERE parent_id IS NULL
6 
7    UNION ALL
8 
9    -- 2. Recursive Member
10    SELECT c.id, c.name, c.parent_id, r.depth + 1
11    FROM category c
12    JOIN cte_name r ON c.parent_id = r.id
13)
14SELECT * FROM cte_name;

Step-by-Step Example 1: Number Generation

To generate a sequence of numbers from 1 to 5:

1WITH RECURSIVE NumberSeq AS (
  -- Anchor
  SELECT 1 AS num
  
  UNION ALL
  
  -- Recursive Step: increment num until condition terminates
  SELECT num + 1
  FROM NumberSeq
  WHERE num < 5
11)
12SELECT num FROM NumberSeq;

Output: 1, 2, 3, 4, 5.

Step-by-Step Example 2: Organizational Hierarchy Traversal

Suppose an employees table has an employee_id and a manager_id pointing to their supervisor:

1CREATE TABLE org_chart (
2    emp_id INT PRIMARY KEY,
3    name VARCHAR(50),
4    manager_id INT NULL
5);
6 
7INSERT INTO org_chart VALUES
8(1, 'Alice (CEO)', NULL),
9(2, 'Bob (VP Tech)', 1),
10(3, 'Charlie (VP Sales)', 1),
11(4, 'David (Dev Manager)', 2),
12(5, 'Eve (Senior Dev)', 4),
13(6, 'Frank (Junior Dev)', 5);

Querying the entire reporting line from the CEO down to junior developers with depth levels:

1WITH RECURSIVE HierarchyCTE AS (
  -- 1. Anchor: Start at the top of the pyramid (CEO)
  SELECT 
      emp_id, 
      name, 
      manager_id, 
      1 AS org_level,
      CAST(name AS CHAR(200)) AS path_trace
  FROM org_chart
  WHERE manager_id IS NULL
11 
  UNION ALL
13 
  -- 2. Recursive Step: Find all direct reports of current level
  SELECT 
      e.emp_id, 
      e.name, 
      e.manager_id, 
      h.org_level + 1,
      CONCAT(h.path_trace, ' -> ', e.name)
  FROM org_chart e
  JOIN HierarchyCTE h ON e.manager_id = h.emp_id
23)
24SELECT org_level, name, path_trace
25FROM HierarchyCTE
26ORDER BY path_trace;

Preventing Infinite Recursion Loops

If cyclical references occur (e.g., Employee A manages Employee B, and Employee B manages Employee A), recursion could loop indefinitely, exhausting memory.

MySQL protects against runaway execution using the cte_max_recursion_depth system variable:

1-- Check current recursion limit (default is 1000)
2SHOW VARIABLES LIKE 'cte_max_recursion_depth';
3 
4-- Adjust recursion depth for large datasets or strict safety
5SET SESSION cte_max_recursion_depth = 50;

If a query exceeds this limit, MySQL immediately aborts with: ERROR 3636 (HY000): Recursive query aborted after 51 iterations.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which keyword must follow WITH to enable recursive query capabilities in MySQL?

Question 2

What are the two mandatory query components connected by UNION ALL inside a recursive CTE?

Question 3

When does a recursive CTE terminate execution?

Question 4

What MySQL system variable protects the database from infinite recursion loops in CTEs?

Question 5

What practical real-world problem are recursive CTEs best suited to solve?

Next Lesson

CTEs vs Subqueries vs Views Comparison

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Chaining Multiple CTEs in a Single Query	CTEs vs Subqueries vs Views Comparison

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

CTEs vs Subqueries vs Views Comparison

Recursive CTEs for Hierarchies & Trees

Intermediate14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Recursive CTEs for Hierarchies & Trees

Standard relational tables store flat sets of rows, yet real-world enterprise data is filled with hierarchies, trees, and graphs:

Organizational reporting charts (CEOs -> VPs -> Managers -> Developers)
Multi-level product categories (Electronics -> Computers -> Laptops -> Gaming Laptops)
Bill of Materials (BOM) in manufacturing (Car -> Engine -> Cylinder -> Piston Rings)

Structure of a Recursive CTE

A recursive CTE consists of three indispensable components:

1
The Anchor Member: An initial non-recursive query that generates the starting base rows (e.g., the top-level CEO or root category).
2
UNION ALL (or UNION): Joins the anchor results to the subsequent recursive iterations.
3
The Recursive Member: A query that references the CTE itself, joining to the base table to fetch the next level of children. It continues executing until it yields an empty set.

1WITH RECURSIVE cte_name AS (
2    -- 1. Anchor Member
3    SELECT id, name, parent_id, 1 AS depth
4    FROM category
5    WHERE parent_id IS NULL
6 
7    UNION ALL
8 
9    -- 2. Recursive Member
10    SELECT c.id, c.name, c.parent_id, r.depth + 1
11    FROM category c
12    JOIN cte_name r ON c.parent_id = r.id
13)
14SELECT * FROM cte_name;

Step-by-Step Example 1: Number Generation

To generate a sequence of numbers from 1 to 5:

1WITH RECURSIVE NumberSeq AS (
  -- Anchor
  SELECT 1 AS num
  
  UNION ALL
  
  -- Recursive Step: increment num until condition terminates
  SELECT num + 1
  FROM NumberSeq
  WHERE num < 5
11)
12SELECT num FROM NumberSeq;

Output: 1, 2, 3, 4, 5.

Step-by-Step Example 2: Organizational Hierarchy Traversal

Suppose an employees table has an employee_id and a manager_id pointing to their supervisor:

1CREATE TABLE org_chart (
2    emp_id INT PRIMARY KEY,
3    name VARCHAR(50),
4    manager_id INT NULL
5);
6 
7INSERT INTO org_chart VALUES
8(1, 'Alice (CEO)', NULL),
9(2, 'Bob (VP Tech)', 1),
10(3, 'Charlie (VP Sales)', 1),
11(4, 'David (Dev Manager)', 2),
12(5, 'Eve (Senior Dev)', 4),
13(6, 'Frank (Junior Dev)', 5);

Querying the entire reporting line from the CEO down to junior developers with depth levels:

1WITH RECURSIVE HierarchyCTE AS (
  -- 1. Anchor: Start at the top of the pyramid (CEO)
  SELECT 
      emp_id, 
      name, 
      manager_id, 
      1 AS org_level,
      CAST(name AS CHAR(200)) AS path_trace
  FROM org_chart
  WHERE manager_id IS NULL
11 
  UNION ALL
13 
  -- 2. Recursive Step: Find all direct reports of current level
  SELECT 
      e.emp_id, 
      e.name, 
      e.manager_id, 
      h.org_level + 1,
      CONCAT(h.path_trace, ' -> ', e.name)
  FROM org_chart e
  JOIN HierarchyCTE h ON e.manager_id = h.emp_id
23)
24SELECT org_level, name, path_trace
25FROM HierarchyCTE
26ORDER BY path_trace;

Preventing Infinite Recursion Loops

If cyclical references occur (e.g., Employee A manages Employee B, and Employee B manages Employee A), recursion could loop indefinitely, exhausting memory.

MySQL protects against runaway execution using the cte_max_recursion_depth system variable:

1-- Check current recursion limit (default is 1000)
2SHOW VARIABLES LIKE 'cte_max_recursion_depth';
3 
4-- Adjust recursion depth for large datasets or strict safety
5SET SESSION cte_max_recursion_depth = 50;

If a query exceeds this limit, MySQL immediately aborts with: ERROR 3636 (HY000): Recursive query aborted after 51 iterations.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which keyword must follow WITH to enable recursive query capabilities in MySQL?

Question 2

What are the two mandatory query components connected by UNION ALL inside a recursive CTE?

Question 3

When does a recursive CTE terminate execution?

Question 4

What MySQL system variable protects the database from infinite recursion loops in CTEs?

Question 5

What practical real-world problem are recursive CTEs best suited to solve?

Next Lesson

CTEs vs Subqueries vs Views Comparison

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Chaining Multiple CTEs in a Single Query	CTEs vs Subqueries vs Views Comparison

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next