Connect by in Oracle SQL

hierarchical-query, oracle, sql

Solution

CONNECT BY PRIOR empno = manager_id;

This will produce the recursion. All records that are part of the next lower hierarchical level will return. This will return a hierarchy from top to bottom for all managers and their respective under working subordinates.

30 (manager_id)
   12 
   5 (manager_id)
      1
      7
20 (manager_id)
   15
   10

Problem

Suppose that we have following tables ``` create table Employee( 2 EMPNO NUMBER(3), 3 ENAME VARCHAR2(15 BYTE), 4 HIREDATE DATE, 5 ORIG_SALARY NUMBER(6), 6 CURR_SALARY NUMBER(6), 7 REGION VARCHAR2(1 BYTE), 8 MANAGER_ID NUMBER(3) 9 ) ``` and ``` create table job ( 2 EMPNO NUMBER(3), 3 jobtitle VARCHAR2(20 BYTE) 4 ) 5 / ``` I am interested in what the below query does ``` SELECT empno, manager_id, ename 2 FROM employee 3 START WITH empno = 1 4 CONNECT BY PRIOR empno = manager_id; ``` As I understood this code selects `empno`,`manager_id`,`ename` from employee, it starts selection from this row where `empno=1`, but could not understand what this line does: ``` CONNECT BY PRIOR empno = manager_id; ``` Is this the same as: ``` where empno=manager_id? ```

Original source