Walking an Org Chart with One SQL Query

Walking an Org Chart with One SQL Query

Some data is naturally arranged like a tree:

• Employees and managers
• Folders and files
• Categories and subcategories
• Comments and replies

A recursive CTE lets SQL walk through that tree.

The table

Imagine an employees table like this:

id	name	manager_id
1	Maya	NULL
2	Jon	1
3	Priya	1
4	Leo	2

manager_id points to another employee in the same table.

The recursive query

WITH RECURSIVE org AS (
    -- Start with the top-level employee
    SELECT
        id,
        name,
        manager_id,
        0 AS level,
        ARRAY[id] AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Find employees who report to someone already found
    SELECT
        e.id,
        e.name,
        e.manager_id,
        org.level + 1,
        org.path || e.id
    FROM employees e
    JOIN org
        ON e.manager_id = org.id
)
SELECT
    repeat('  ', level) || name AS employee
FROM org
ORDER BY path;

The result:

Maya
  Jon
    Leo
  Priya

How it works

The first SELECT finds the starting point: employees without a manager.

The second SELECT finds their direct reports. PostgreSQL then repeats that step for each new employee until there are no more people to find.

level controls the indentation. path keeps the hierarchy in the right order.

That is the basic pattern:

1. Find the starting rows.
2. Join the table back to the CTE.
3. Repeat until the next level is empty.

Recursive CTEs can look unusual at first, but they are a clean way to query hierarchical data without writing separate queries for every level.