Recursive CTE concept in SQL

Introduction

Recursive CTE helps you repeat a query step by step until you get all related results. It is like following a chain of links one by one.

Finding all managers above an employee in a company hierarchy.

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

Exploring family trees to find ancestors or descendants.

Calculating factorial or Fibonacci numbers step by step in SQL.

Syntax

SQL

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

The first SELECT is the starting point (anchor).

The second SELECT repeats using results from the first, building step by step.

Examples

This example counts from 1 to 5 by adding 1 each time.

SQL

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 returns no rows: the whole CTE returns no rows.

SQL

WITH RECURSIVE empty_cte(n) AS (
  SELECT 1 WHERE false
  UNION ALL
  SELECT n + 1 FROM empty_cte WHERE n < 3
)
SELECT * FROM empty_cte;

Here the recursion stops immediately because the condition is false at start.

SQL

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

Sample Program

This query finds the chain of employees managed by employee 1, showing each level down.

SQL

WITH RECURSIVE employee_hierarchy(employee_id, manager_id, level) AS (
  -- Anchor: start with employee 1
  SELECT employee_id, manager_id, 1 AS level
  FROM employees
  WHERE employee_id = 1
  
  UNION ALL
  
  -- Recursive: find employee managed by current employee
  SELECT e.employee_id, e.manager_id, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level;

Important Notes

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

Time complexity depends on how many levels or rows the recursion goes through.

Common mistake: forgetting the anchor query or the stopping condition causes infinite recursion.

Use recursive CTEs when you need to explore hierarchical or linked data step by step.

Summary

Recursive CTEs let you repeat queries stepwise to explore linked data.

They have an anchor (start) and recursive part (repeat).

Useful for hierarchies, chains, and stepwise calculations.