CodeRang
SQL topics

Recursive CTE in SQL with WITH RECURSIVE

A recursive CTE lets a SQL query use rows from one iteration to produce the next set of rows. This example shows how WITH RECURSIVE works with an anchor member, a recursive member, hierarchy depth, and a generated path.

Updated · SQL Arena team

CTE trackAll problems

What you will practice

  • Separate the anchor and recursive members
  • Traverse a hierarchy from parent to child
  • Calculate hierarchy depth
  • Build a path while recursion progresses
  • Recognize when cycle protection is required

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Traverse the organization below the CTO

In employees, manager_id points to another employee. Starting from id = 2, return that employee and every descendant at any depth. Output employee_name, depth, and path. The starting employee must have depth 0, and path must show the chain of names from the CTO to each row.

Query 1PostgreSQLv16
WITH RECURSIVE org AS (
  SELECT id, employee_name, 0 AS depth, ARRAY[employee_name]::text[] AS path FROM employees WHERE id = 2
  UNION ALL
  SELECT e.id, e.employee_name, o.depth + 1, o.path || e.employee_name
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT employee_name, depth, array_to_string(path, ' > ') AS path
FROM org
ORDER BY depth, employee_name;
Submit
Resultemployeesemployee_badges
Answer accepted

How a recursive CTE works in SQL

The org CTE has an anchor member and a recursive member. The anchor runs once and selects Leo, the employee with id = 2. It also initializes depth to 0 and creates a one-element path array. Changing the anchor changes the root of the entire traversal.

After UNION ALL, the recursive member joins employees to rows already produced by org. The condition e.manager_id = o.id finds direct reports of the current level. Each new row increments depth and appends the employee name to the path. The next iteration applies the same logic to those new employees, so grandchildren are discovered without adding another JOIN for every level.

The outer SELECT formats the array as a readable path and explicitly orders rows by depth and employee name. That ORDER BY is important because recursive evaluation does not give you a presentation order. Production hierarchy queries should also consider bad data. If relationships can contain cycles, keep visited ids in the recursive state and reject a node that already appears in the path.

A recursive CTE normally has two parts. The anchor member returns the starting rows, while the recursive member refers back to the CTE and produces the next level. UNION ALL commonly combines them. SQL keeps evaluating the recursive member until an iteration produces no additional rows. This pattern is useful for organizational charts, category trees, parent-child relationships, paths, and sequences whose depth is not known beforehand. Recursive queries also need careful termination logic, especially when the underlying data can contain cycles.

SQLPostgreSQL v16
WITH RECURSIVE org AS (
  SELECT id, employee_name, 0 AS depth, ARRAY[employee_name]::text[] AS path FROM employees WHERE id = 2
  UNION ALL
  SELECT e.id, e.employee_name, o.depth + 1, o.path || e.employee_name
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT employee_name, depth, array_to_string(path, ' > ') AS path
FROM org
ORDER BY depth, employee_name;
Query result
employee_namedepthpath
Leo0Leo
Omar1Leo > Omar
Sara1Leo > Sara
Emma2Leo > Omar > Emma
Ivan2Leo > Omar > Ivan
Noah2Leo > Sara > Noah

Common recursive CTE mistakes

01

Joining every iteration back to the root

If the recursive member keeps looking for rows whose manager_id equals the original CTO id, it will only reproduce the first level. The recursive join must use the ids produced by the previous iteration.

02

Using UNION as accidental cycle protection

UNION removes duplicate rows, but that does not make every cyclic hierarchy safe. It may also add unnecessary deduplication work. Use UNION ALL when duplicates are valid, and handle cycle detection deliberately when the data can loop.

03

Relying on recursive evaluation order

A recursive query may discover rows in an order that looks useful during testing, but that order is not a result guarantee. Sort in the outer query when you need deterministic output by level, name, or path.

Practice problems on this topic

CTE track

Once the basic traversal works, change the problem instead of repeating the same query. Count a manager's complete team, label every employee with an organization depth, walk upward through a classification tree, or construct breadcrumbs from a node to its ancestors. SQL Arena includes hierarchy problems built around those variations. Continue with the track “CTE: запрос из нескольких шагов” for a broader sequence of multi-step CTE exercises. The trainer contains 1,192 tasks, and solutions are checked against hidden data rather than matched to one expected query.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is a recursive CTE in SQL?

A recursive CTE is a common table expression that can reference its own result. It builds a result iteratively, starting with anchor rows and repeatedly applying a recursive query.

How does WITH RECURSIVE work?

The anchor member runs first and creates the initial rows. The recursive member then uses rows from the previous iteration to produce more rows until no new rows are returned.

When should I use a recursive CTE?

Recursive CTEs are useful for hierarchical or linked data with an unknown number of levels. Typical examples include employee trees, nested categories, paths, and parent-child structures.

What is the difference between a CTE and a recursive CTE?

A regular CTE defines a named query result without referring to itself. A recursive CTE references its own result so that the query can process data across multiple iterations.

See also

Practice recursive CTE queries

SQL Arena lets you run PostgreSQL in the browser and check each solution automatically. The free tier includes 292 tasks across all four supported dialects with no card required. Harder hierarchy exercises are available in Premium, where hidden-data checks make it harder to pass with a query that only works for the visible example.

Practice SQL

More practice from CodeRang