Recursive CTEs in MySQL

Introduction

Recursive CTEs help you repeat a query step by step until you get all related data. It is like following a chain of connections one by one.

Finding all employees under a manager in a company hierarchy.

Listing all parts needed to build a product from smaller parts.

Exploring family trees to find ancestors or descendants.

Calculating sequences like factorial or Fibonacci numbers in SQL.

Syntax

MySQL

WITH RECURSIVE cte_name (column_list) AS (
  -- Anchor member: base query
  SELECT initial_columns
  FROM table_name
  WHERE condition
  UNION ALL
  -- Recursive member: query referring to cte_name
  SELECT next_columns
  FROM table_name JOIN cte_name ON join_condition
  WHERE recursive_condition
)
SELECT * FROM cte_name;

The anchor member is the starting point of recursion.

The recursive member repeats the query using results from the previous step.

Examples

This example generates numbers from 1 to 5 using recursion.

MySQL

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

This shows what happens if the anchor query returns no rows: the whole CTE returns no rows.

MySQL

WITH RECURSIVE empty_cte AS (
  SELECT 1 WHERE FALSE
  UNION ALL
  SELECT 2 FROM empty_cte
)
SELECT * FROM empty_cte;

This example has only one element because the recursive condition stops immediately.

MySQL

WITH RECURSIVE single_node AS (
  SELECT 1 AS id
  UNION ALL
  SELECT id + 1 FROM single_node WHERE id < 1
)
SELECT * FROM single_node;

This example shows recursion stopping at the end of the chain (id = 3).

MySQL

WITH RECURSIVE chain AS (
  SELECT 1 AS id
  UNION ALL
  SELECT id + 1 FROM chain WHERE id < 3
)
SELECT * FROM chain;

Sample Program

This query finds all employees under the top manager, showing their level in the hierarchy.

MySQL

WITH RECURSIVE employee_hierarchy AS (
  -- Anchor member: select the top manager
  SELECT employee_id, manager_id, employee_name, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- Recursive member: select employees reporting to previous level
  SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, manager_id, employee_name, level
FROM employee_hierarchy
ORDER BY level, employee_id;

Important Notes

Recursive CTEs run until no new rows are added, so be careful to avoid infinite loops.

Time complexity depends on the depth and branching of recursion; it can grow quickly.

Common mistake: forgetting the anchor member or recursive condition, causing no results or infinite recursion.

Use recursive CTEs when you need to process hierarchical or sequential data inside SQL.

Summary

Recursive CTEs let you repeat queries step by step to explore connected data.

They have an anchor part and a recursive part that refers back to the CTE.

Useful for hierarchies, sequences, and tree-like data in databases.