cte

Recursive CTE: Employee Hierarchy

Walk the manager tree recursively to list each employee and their management chain depth.

Overview

Recursive CTEs shine for hierarchical data stored with parent pointers — like employees.manager_id. We start from top-level managers (manager_id IS NULL) and recursively join to their reports, tracking depth. This is the canonical "org chart" query.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

Anchor: WHERE manager_id IS NULL selects the top-level managers.
Recursive step: joins employees to the growing org CTE on manager_id = org.id, adding one level of reports each pass.
depth increments per level, showing how far down the tree each person sits.

Cycles will hang

A cyclic manager_id chain (A manages B manages A) causes infinite recursion. Real schemas enforce acyclic trees.

Variations

Show the management path as a concatenated string:

Employees & Departments

Loading database engine...

Common Mistakes

Missing anchor filter. Without WHERE manager_id IS NULL the recursion has no defined starting point.
Cycles. Self-referential manager loops cause runaway recursion.
Wrong join direction. Joining org.id = e.id instead of e.manager_id = o.id won't walk down the tree.