Connect by in Oracle SQL

Question

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?

[Docs](http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm) — turbanoff, Jun 21 '12 at 06:32

score 12 · Accepted Answer · edited Jul 03 '15 at 16:38

12

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

edited Jul 03 '15 at 16:38

Umber Ferrule

3,358
6
35
38

answered Jun 21 '12 at 06:43

manurajhada

5,284
3
24
43

1

so how can i guess if i need code like this to select something? – Jun 21 '12 at 07:26
1

Simply when ever you want to get something which has parent-child relation in hierarchical manner. – manurajhada Jun 21 '12 at 07:42

Nir Alfasi · Answer 2 · 2018-04-04T18:30:59.060

6

The query is recursive, it start from employee #1 (CEO probably) and then recursively prints all his subordinates and then all their subordinates and so on and so forth (until all the employees are printed).

A good explanation about the "START WITH" and "CONNECT BY" can be found here

edited Apr 04 '18 at 18:30

answered Jun 21 '12 at 06:33

Nir Alfasi

53,191
11
86
129

Connect by in Oracle SQL

2 Answers2

Linked

Related