Hierarchical query on employees and their supervisor in Oracle HR

Hierarchical query on employees and their supervisor in Oracle HR

We all know the famous emp/dept hierarchical queries to show employees belonging to a department and employees with their manager. I recently came across a question on how to query all employees and their managers (called supervisors in HR) in a hierarchical manor so I created this query as combination of what I've read in https://technology.amis.nl/2005/08/16/hierarchical-query-with-nodes-from-different-tables-dept-and-emp-nodes-in-one-tree/) and Oracle database schema.

Note that the employee - supervisor link can be circular. In that case you will not find those employees in the tree, since there is no top node where the manager (supervisor_id) is empty.

with emps as
(
-- The managers
select s.person_id empno
,      s.first_name || ' ' || s.last_name ename
,      sa.supervisor_id mgr
from   per_people_x s
, per_assignments_x sa
where    s.person_id = sa.person_id
and exists (select 1 from per_assignments_x a where a.supervisor_id = s.person_id)
union
-- The employees
select p.person_id empno -- all EMP-nodes
,      p.first_name || ' ' || p.last_name ename
,      a.supervisor_id mgr
from   per_people_x p, per_assignments_x a
where   p.person_id = a.person_id
)
select lpad(' ', level*3)||ename ename
from   emps
connect
by     prior empno = mgr
start
with  
nvl (mgr,-1) = -1
/