How to traverse Hierarchical data in Oracle?

Problem Statement: You need to traverse the hierarchy data from top to bottom marking the level of each row in the hierarchy.

Solution:

Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.

Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR operator to specify the column/s in the parent row that have the same values as the linking columns in the child row.

In order to loop through the entire hieratchy, remove the START WITH clause. This will fetch all the students with their professor and their professor’s professor(i.e. Dean), and so on, until the top manager/director of the institute.

Example 1: Identify the hierarchical/structure for the professor_id = 100 SQL:

/*
     Function - Identify the hierarchical/structure for the professor_id = 102
  Tables Used - students
         Data - documented below
*/

SELECT student_id,
       level,
       lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
  FROM students
START WITH professor_id = 102
CONNECT BY professor_id = PRIOR student_id;

Output

103 1   BROWN MICHAEL
104 2     -JONES WILLIAM
105 2     -MILLER DAVID
106 2     -DAVIS RICHARD
107 2     -GARCIA CHARLES

Example 2: Identify the hierarchical/structure where professor_id is NULL SQL:

/*
     Function - Identify the hierarchical/structure professor_id is null
  Tables Used - students
         Data - documented below
*/

SELECT student_id,
       level,
       lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
  FROM students
START WITH professor_id IS null
CONNECT BY professor_id = PRIOR student_id;

Output : Few rows from above SQL

100 1   SMITH JAMES
101 2     -JOHNSON JOHN
108 3        -RODRIGUEZ JOSEPH
109 4           -WILSON THOMAS
110 4           -MARTINEZ CHRISTOPHER
111 4           -ANDERSON DANIEL
112 4           -TAYLOR PAUL
113 4           -THOMAS MARK
102 2     -WILLIAMS ROBERT
103 3        -BROWN MICHAEL
104 4           -JONES WILLIAM
105 4           -MILLER DAVID
106 4           -DAVIS RICHARD
107 4           -GARCIA CHARLES

Example 3: Identify the Institute entire hierarchical/structure SQL:

/*
     Function - Identify the Institute entire hierarchical/structure
  Tables Used - students
         Data - documented below
*/

SELECT student_id,
       level,
       lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
  FROM students
CONNECT BY student_id = PRIOR professor_id;

Output: Few rows from above SQL

100 1   SMITH JAMES
101 1   JOHNSON JOHN
100 2     -SMITH JAMES
102 1   WILLIAMS ROBERT
100 2     -SMITH JAMES
103 1   BROWN MICHAEL
102 2     -WILLIAMS ROBERT
100 3        -SMITH JAMES
104 1   JONES WILLIAM
103 2     -BROWN MICHAEL
102 3        -WILLIAMS ROBERT
100 4           -SMITH JAMES
105 1   MILLER DAVID
103 2     -BROWN MICHAEL
102 3        -WILLIAMS ROBERT
100 4           -SMITH JAMES
106 1   DAVIS RICHARD
103 2     -BROWN MICHAEL
102 3        -WILLIAMS ROBERT
100 4           -SMITH JAMES
107 1   GARCIA CHARLES
103 2     -BROWN MICHAEL
102 3        -WILLIAMS ROBERT
100 4           -SMITH JAMES
108 1   RODRIGUEZ JOSEPH
101 2     -JOHNSON JOHN
100 3        -SMITH JAMES
109 1   WILSON THOMAS
108 2     -RODRIGUEZ JOSEPH
101 3        -JOHNSON JOHN
100 4           -SMITH JAMES
110 1   MARTINEZ CHRISTOPHER
108 2     -RODRIGUEZ JOSEPH
101 3        -JOHNSON JOHN
100 4           -SMITH JAMES
111 1   ANDERSON DANIEL
108 2     -RODRIGUEZ JOSEPH
101 3        -JOHNSON JOHN
100 4           -SMITH JAMES
112 1   TAYLOR PAUL
108 2     -RODRIGUEZ JOSEPH
101 3        -JOHNSON JOHN
100 4           -SMITH JAMES
113 1   THOMAS MARK
108 2     -RODRIGUEZ JOSEPH
101 3        -JOHNSON JOHN
100 4           -SMITH JAMES

Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.

Example

DROP TABLE students;
COMMIT;

CREATE TABLE students
    ( student_id     NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25) 
    , email          VARCHAR2(40) 
    , phone_number   VARCHAR2(20)
    , join_date      DATE 
    , class_id       VARCHAR2(20)  
    , fees           NUMBER(8,2)
    , professor_id   NUMBER(6)
    , department_id  NUMBER(4) 
    ) ;

Example

CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);

COMMIT;