How does the hierarchy with a recursive cte query work?

The first SELECT is the anchor: the people with no manager, at depth 1. The second SELECT is joined to the rows found so far and adds the next level, repeating until no new rows appear. WHERE org.depth < 20 is a safety limit, and path builds the chain of names from the top down.

What commonly goes wrong with hierarchy with a recursive cte in SQL?

A cycle (A reports to B, B reports to A) loops forever without a depth limit.

Hierarchy with a Recursive CTE in SQL

Q: What is hierarchy with a recursive cte in SQL?

A recursive CTE walks a hierarchy such as an org chart or category tree, returning every level and how deep each row sits.

Q: What commonly goes wrong with hierarchy with a recursive cte in SQL?

A cycle (A reports to B, B reports to A) loops forever without a depth limit.

A recursive CTE walks a hierarchy such as an org chart or category tree, returning every level and how deep each row sits.

Example query

This example assumes: employees(id bigint, name text, manager_id bigint). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth, name::text AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.depth + 1, org.path || ' > ' || e.name
  FROM employees e
  JOIN org ON e.manager_id = org.id
  WHERE org.depth < 20
)
SELECT id, name, depth, path
FROM org
ORDER BY path;

How it works

The first SELECT is the anchor: the people with no manager, at depth 1.
The second SELECT is joined to the rows found so far and adds the next level, repeating until no new rows appear.
WHERE org.depth < 20 is a safety limit, and path builds the chain of names from the top down.

MySQL, BigQuery, SQL Server and SQLite differences

WITH RECURSIVE works in PostgreSQL, MySQL 8+, SQLite and BigQuery. SQL Server omits the word RECURSIVE (WITH org AS (...)) and uses + instead of || for text. Oracle uses CONNECT BY or recursive subquery factoring.

Common mistakes

A cycle (A reports to B, B reports to A) loops forever without a depth limit.
The anchor decides the start: a tree with several roots, or an orphan whose manager is missing, may be left out.
Recursive queries can be slow on very large hierarchies; limit the depth you need.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show the reporting chain for every employee.
How many people are under each director, at any level?