Why does Oracle yield multiple levels per row in this hierarchical query

hierarchyoracle

I'm trying to reproduce the first simple example from this tutorial.

However, I don't understand why I get so many rows back. I would expect that 1 reports to 2 only at level 1 and not at level 2 as well.

SQL> select employee_id, reports_to from employees;

EMPLOYEE_ID REPORTS_TO
----------- ----------
      1      2
      2
      3      2
      4      2
      5      2
      6      5
      7      5
      8      2
      9      5

9 rows selected.

SQL> select employee_id, reports_to, LEVEL from employees connect by prior employee_id = reports_to;

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      1      2      1
      8      2      1
      5      2      1
      6      5      2
      9      5      2
      7      5      2
      4      2      1
      3      2      1
      6      5      1
      9      5      1
      7      5      1

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      2         1
      1      2      2
      8      2      2
      5      2      2
      6      5      3
      9      5      3
      7      5      3
      4      2      2
      3      2      2

20 rows selected.

Best Answer

You're getting many rows because without START WITH it outputs all possible hierarchies . You see 1 reports 2 on level 1 (1->2), and another one on level 2 (1->2->NULL). Adding SYS_CONNECT_BY_PATH(reports_to, '/') "Path" to select will help in visualizing that.
Let's connect just 2 rows for illustration: 

with test1 as 
(
select 1 as employee_id, 2 as reports_to from dual
union all 
select 2 as employee_id, NULL as reports_to from dual


)
select employee_id,reports_to ,level ,SYS_CONNECT_BY_PATH(NVL(to_char(reports_to),'NULL'), '/') "Path"

from test1 
--start with employee_id=2
connect by prior employee_id  =reports_to;

will output : 

Employee    ReportTo   Level Path
1              2        1     /2  
2              <null>   1     /NULL  
1              2        2     /NULL/2

If we uncomment "start with" , the result will be 

2   <null>  1   /NULL
1      2    2   /NULL/2
Related Question