Walk a hierarchy

Use a recursive CTE to walk a parent-child relationship.

Hierarchy query

With a recursive CTE you query manager-employee hierarchies. Each person reports to a parent.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

This query produces the organization tree.

WITH RECURSIVE defines a self-referencing temporary result set (CTE) named org; the query after it uses it like a table. All columns are selected from org.

Hierarchy query — details

The anchor picks the top manager, the recursive adds each subordinate level. level shows depth.

In the anchor WHERE manager_id IS NULL picks the person with no manager; in the recursive part employees are joined to those whose manager was found on the previous level.

Exercise Hierarchy query

Write an org hierarchy query: anchor the root, recursively add subordinates.

Show example solution

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

Test yourself

Walk a hierarchy: match the terms

hierarchy

tree
manager

parent
subordinate

child
level

depth

Add level

  SELECT id, name, manager_id, 1 AS ____

Show answer

  SELECT id, name, manager_id, 1 AS level

What does the anchor pick in a hierarchy query?
- The top person
- Everyone
- The bottom person
- The leaves
Show answer

The top person — The anchor picks the top person.

Next lesson: Tracking depth

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies