Trees: org charts with CONNECT BY and recursive WITH

A tree is a table where each row points at its parent: employees and managers, folders and subfolders, parts and assemblies. The questions are always the same: show it indented, find everyone under someone, find the path to the top.

Our cricket data is flat, so here's the league office, with each person's manager:

Setup

begin
  for t in (select table_name from user_tables where table_name = 'D130_STAFF') loop
    execute immediate 'drop table d130_staff purge';
  end loop;
end;
/

Setup

create table d130_staff (
  staff_id   number primary key,
  name       varchar2(30) not null,
  title      varchar2(30) not null,
  manager_id number references d130_staff
);

Setup

insert into d130_staff (staff_id, name, title, manager_id)
select 1,  'Meera Kapoor',   'Commissioner',       null from dual union all
select 2,  'Daniel Okafor',  'Head of Cricket',    1    from dual union all
select 3,  'Grace Mwangi',   'Head of Operations', 1    from dual union all
select 4,  'Rohan Desai',    'Chief Umpire',       2    from dual union all
select 5,  'Priya Nair',     'Head of Analytics',  2    from dual union all
select 6,  'Sam Patel',      'Venue Manager',      3    from dual union all
select 7,  'Nadia Rossi',    'Ticketing Lead',     3    from dual union all
select 8,  'Ana Silva',      'Umpire',             4    from dual union all
select 9,  'Tom Hughes',     'Umpire',             4    from dual union all
select 10, 'Liam Chen',      'Data Analyst',       5    from dual union all
select 11, 'Zara Ahmed',     'Data Analyst',       5    from dual union all
select 12, 'Jack Moore',     'Groundskeeper',      6    from dual;

Test 1: the org chart, indented

Everyone, top-down, each person under their manager, colleagues in alphabetical order: their level, name indented two spaces per level, title, and whether they manage nobody.

The analysts are leaves. They'd like you to know they're actually the roots of everything.

Way 1: CONNECT BY

select level as lvl,
       lpad(' ', 2 * (level - 1)) || s.name as name,
       s.title,
       case connect_by_isleaf when 1 then 'Y' else 'N' end as leaf
from   d130_staff s
start  with s.manager_id is null
connect by prior s.staff_id = s.manager_id
order  siblings by s.name;

• START WITH picks the root(s); CONNECT BY PRIOR staff_id = manager_id means "the next rows are the ones whose manager is the row I just found".
• LEVEL is the depth (1 at the top), CONNECT_BY_ISLEAF is 1 for people with no reports.
• ORDER SIBLINGS BY sorts within each manager, keeping the tree shape. A plain ORDER BY would scramble it.

Way 2: recursive WITH and SEARCH DEPTH FIRST

with tree (staff_id, name, title, lvl) as (
  select staff_id, name, title, 1 from d130_staff where manager_id is null
  union all
  select s.staff_id, s.name, s.title, t.lvl + 1
  from   d130_staff s
  join   tree t on s.manager_id = t.staff_id
)
search depth first by name set ord
select t.lvl,
       lpad(' ', 2 * (t.lvl - 1)) || t.name as name,
       t.title,
       case when exists (select 1 from d130_staff c where c.manager_id = t.staff_id) then 'N' else 'Y' end as leaf
from   tree t
order  by ord;

SEARCH DEPTH FIRST BY name SET ord numbers the rows in tree order (each person followed by everyone below them, siblings by name); ordering by ord gives the indented chart. SEARCH BREADTH FIRST would list each level in turn instead.

Test 2: who is under whom

For each manager, how many people are below them at any depth, and the path from the top.

Way 1: CONNECT_BY_ROOT

select root_name as name, count(*) - 1 as below,
       max(root_path) as path
from   (select connect_by_root name as root_name,
               connect_by_root staff_id as root_id
        from   d130_staff
        connect by prior staff_id = manager_id) r
join   (select staff_id, ltrim(sys_connect_by_path(name, ' > '), ' > ') as root_path
        from   d130_staff
        start  with manager_id is null
        connect by prior staff_id = manager_id) p on p.staff_id = r.root_id
group  by root_name
having count(*) > 1
order  by below desc, name;

• Without START WITH, every row is a root, so each person's tree is walked once. CONNECT_BY_ROOT name stamps each row with whose tree it's in; counting per root (minus the root itself) gives everyone below.
• SYS_CONNECT_BY_PATH(name, ' > ') builds the path from the top down to each row.

Way 2: recursive WITH carrying the root

with walk (root_id, staff_id) as (
  select staff_id, staff_id from d130_staff
  union all
  select w.root_id, s.staff_id from walk w join d130_staff s on s.manager_id = w.staff_id
),
paths (staff_id, path) as (
  select staff_id, cast(name as varchar2(400)) from d130_staff where manager_id is null
  union all
  select s.staff_id, p.path || ' > ' || s.name from paths p join d130_staff s on s.manager_id = p.staff_id
)
select s.name, count(*) - 1 as below, max(p.path) as path
from   walk w
join   d130_staff s on s.staff_id = w.root_id
join   paths p      on p.staff_id = w.root_id
group  by s.name
having count(*) > 1
order  by below desc, s.name;

The same two ideas, built by hand: one recursion remembers where each walk started, the other glues names onto the path as it goes down. The CAST sets the path column wide enough for the longest path.

19c and 26ai

The same on both.

Gotchas

• ORDER SIBLINGS BY name can mean the wrong name. Our output column is also called name (the indented one, built from LEVEL), and an unqualified name in ORDER BY picks the column alias first. Sorting siblings by an expression with LEVEL in it gives ORA-00976, which is what our first 19c run hit. ORDER SIBLINGS BY s.name says "the table's column".

• Cycles. If Meera's manager were set to Jack, the tree would loop forever: CONNECT BY raises ORA-01436, recursive WITH ORA-32044. Use CONNECT BY NOCYCLE (with CONNECT_BY_ISCYCLE) or the CYCLE staff_id SET is_cycle TO 'Y' DEFAULT 'N' clause.

• WHERE in a CONNECT BY query filters after the tree is built. Removing a manager with WHERE keeps their reports; put the condition in CONNECT BY to prune the whole branch.

• The recursive WITH column list is required, and the anchor's types set the columns' types: hence the CAST on path.