An example of connect by recursive query

1. connect by demo

Recursively view organizational relationships among all employees

select lpad(' ', level * 2 - 1, ' ') || emp.last_name emp_last_name,
-- level Pseudo column holds recursive depth value   level =2 Indicates recursion of 2-1 Layer, that is, query only KING id All direct reports for 100 are shown in the pseudo column below
--Here, the recursion depth relationship is represented by a space
       emp.first_name emp_first_name,
       emp.employee_id,
       emp.manager_id,
       mgr_last_name
  from (select /*+ inline gather_plan_statistics*/e.last_name, e.first_name, e.employee_id, e.manager_id,
       es.last_name mgr_last_name
          from employees e  
          left outer join employees es
            on es.employee_id = e.manager_id) emp
connect by prior emp.employee_id = emp.manager_id
-- extend  prior On the left is to query the lower level, and on the right is to query the upper level
 start with emp.manager_id is null
 order siblings by emp.last_nameL;

Extension: order siblings by means that the sorting between siblings is only for the result set of tree structure,
See for details https://blog.csdn.net/niuhea/article/details/8596307
The query results are as follows:

2. level pseudo column demonstration

select   emp.last_name emp_last_name,
       emp.first_name emp_first_name,
       emp.employee_id,
       emp.manager_id,
       mgr_last_name
  from (select /*+ inline gather_plan_statistics*/e.last_name, e.first_name, e.employee_id, e.manager_id,
       es.last_name mgr_last_name
          from employees e  
          left outer join employees es
            on es.employee_id = e.manager_id) emp
            where level =2
connect by prior emp.employee_id = emp.manager_id
 start with emp.manager_id is null
 order siblings by emp.last_name;

Because start with specifies that the recursion starts from King, and level null recursion depth, that is to say, only all direct subordinates of king are found, 14 people in total,
Query results:

3. Extension Demo: query superior

View all superiors of all 206 employees

select   emp.last_name emp_last_name,
       emp.first_name emp_first_name,
       emp.employee_id,
       emp.manager_id,
       mgr_last_name
  from (select /*+ inline gather_plan_statistics*/e.last_name, e.first_name, e.employee_id, e.manager_id,
       es.last_name mgr_last_name
          from employees e  
          left outer join employees es
            on es.employee_id = e.manager_id) emp
connect by  emp.employee_id = prior emp.manager_id
 start with  emp.employee_id='206'
 order siblings by emp.last_name;

Also, you can use level to control whether to view all superiors, direct superiors or superior superiors