PPC - SomeOne SpenDing - SomeOne EarNing

Learn Oracle SQL : The Hierarchical Query Clause

by Rocky Jagtiani

Oracle Certified Associate

I train on Oracle Certification from last 7 years

For understanding the Hierarchical Query Clause , make sure of :

1> Installing Oracle-11g ( steps of installation and Oracle-11g download copy is here , please see      Oracle SQL - Pl/SQL > downloads section ). By the way any version from 9i and upwards would do.
2> Reading each and every step.
3> & finally Executing as you read on. 

Lets create some data sets to play with :

create table hierarchy(
empno int not null unique,
ename varchar2(20) not null,
Job varchar2(20) not null, 
mgr int,
deptno int not null
);

// Add some records to the above table
// Assume deptno : 100 -> HR and 101 -> Training
insert into hierarchy values(1,'Simran', 'director', null, 100); 

insert into hierarchy values(2,'Rocky', 'Training-Head', 1, 101); 

insert into hierarchy values(3,'Neetu', 'IT Recruiter', 1, 100);

insert into hierarchy values(4,'Prajakta', 'IT recruiter', 1, 100); 

insert into hierarchy values(5,'Vipin','Web_Mobile_Dev', 2, 101); 

insert into hierarchy values(6,'Anant', 'Office Admin', 1, 100);

insert into hierarchy values(7,'Lokesh', 'Data Scientist', 2, 101);

insert into hierarchy values(8,'Niraj','Data Ananlyst', 2, 101);

insert into hierarchy values(9,'Stephen','Sr. Web Dev', 2, 101);

insert into hierarchy values(10,'Ashish','Project Manager', 2, 101);

insert into hierarchy values(11,'Ganesh','Office Boy', 1, 100);

insert into hierarchy values(12,'Pravin','Office Boy', 1, 100);

Now lets find the employee details and their managers (Remember every employee has a column "mgr", which indicates the emp_id of the manager they report to. More-ever the manager is also an employee).

There are 2 ways to solve this :

First Way : We would use self Join, to join the employee table to itself like this :

select e1.empno, e1.ename, e1.job, e2.ename as Manager 
from hierarchy e1
JOIN 
hierarchy e2 
ON
e1.mgr = e2.empno;

w.r.t above query we would get only n-1 records, i.e we won't get the top-most employee of the Co. i.e the "boss", who does not report to any manager. See below out of 12 , we get only 11 rows.

Solution : try changing from Inner Join to Left Join. Left, Right and Full are 3 types of Outer Join. So, our modified query would look like this :

select e1.empno, e1.ename, e1.job, e2.ename as Manager
from hierarchy e1
LEFT JOIN 
hierarchy e2 
ON
e1.mgr = e2.empno;   

See below, we get all 12 rows. The "Boss" has no Manager. She is the Boss after-all.☺

Above o/p does not clearly show the record hierarchy . Such Hierarchies can be more conveniently shown using "Start with" & "connect by" clauses.

The query using "Start with" & "connect by" clause would look like this : 
 
select empno, ename, job
from hierarchy
start with mgr is null
connect by mgr = prior empno;   

Here start with indicates : The record you would start with. There by
start with mgr is null 
would get us the employee at the top of the hierarchy.

Now, we wish to connect every record to their parent.
i.e we wish to create a parent-child relationship. use
connect by
There by we would be able to find the the employees who work for a manager. So
connect by mgr = prior empno   
would build the hierarchy.  See o/p pic below.

The above o/p does not show the manager_Name. Let's rewrite it, like this :

select empno, ename, job, prior ename as manager
from hierarchy
start with mgr is null
connect by mgr = prior empno;

So, there you have it. Now you know how to write Hierarchical queries in Oracle.

To know more about the training's my team takes and great resources : see here

Have something to add? Great, share your thoughts in the comments section below.