hierarchical queries

A relational database does not store data in a hierarchical way. Then how do I get the data in a hierarchical manner? Here we get to know about how to use the hierarchical querying feature which Oracle has given. This article talks about how you can interpret the hierarchical query conceptually and build hierarchical queries catering your needs.

Using hierarchical queries, you can retrieve records from a table by their natural relationship. Be it a family tree or a employee/manager tree or what ever.

Tree walking enables you to construct a hierarchical tree if the relationship lie in the same table. For instance, a manager column which exists in the emp table which defines the managerial hierarchy.

We shall take up an example of the emp table in Scott schema. Here King is top most in the hierarchy

empno	ename	job	mgr	hiredate
7369	SMITH	CLERK	7902	17-Dec-80
7499	ALLEN	SALESMAN  7698	20-Feb-81
7521	WARD	SALESMAN  7698	22-Feb-81
7566	JONES	MANAGER	7839	2-Apr-81
7654	MARTIN	SALESMAN  7698	28-Sep-81
7698	BLAKE	MANAGER	7839	1-May-81
7782	CLARK	MANAGER	7839	9-Jun-81
7788	SCOTT	ANALYST	7566	19-Apr-87
7839	KING	PRESIDENT	17-Nov-81
7844	TURNER	SALESMAN  7698	8-Sep-81
7876	ADAMS	CLERK	7788	23-May-87
7900	JAMES	CLERK	7698	3-Dec-81
7902	FORD	ANALYST	7566	3-Dec-81
7934	MILLER	CLERK	7782	23-Jan-82

If we have to query the employees reporting to King directly,

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
WHERE  mgr = 7839

7566	JONES	MANAGER	7839	2-Apr-81
7698	BLAKE	MANAGER	7839	1-May-81
7782	CLARK	MANAGER	7839	9-Jun-81

But if we have to walk down the tree and check who all are reporting to Jones, Blake and Clark (recursively)

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

We will quickly see what are all the key words used in this query.

START WITH – Specifies the root rows of the hierarchy or in other words, where to start parsing from. This clause is necessary for true hierarchical queries

CONNECT BY PRIOR – This explains the relationship between the parent and the child.

PRIOR – This is used to achieve the recursive condition (The actual walking)

Direction of walking the tree

To explain more on the CONNECT BY clause, this is used to determine if you are walking from top to bottom or bottom to top.

CONNECT BY PRIOR col_1 = col_2

If walking from top to bottom
col_1 is the parent Key(One which identifies the parent) and col_2 is the child key (this identifies the child)
And here it is

CONNECT BY PRIOR empno = mgr

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate,
 level 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

Gets me this result

7839	KING      PRESIDENT 	           17-Nov-81	1
7566	JONES     MANAGER   7839	2-Apr-81	2
7788	SCOTT     ANALYST   7566	19-Apr-87	3
7876	ADAMS     CLERK     7788	23-May-87	4
7902	FORD      ANALYST   7566	3-Dec-81	3
7369	SMITH     CLERK     7902	17-Dec-80	4
7698	BLAKE     MANAGER   7839	1-May-81	2
7499	ALLEN     SALESMAN  7698	20-Feb-81	3
7521	WARD      SALESMAN  7698	22-Feb-81	3
7654	MARTIN    SALESMAN  7698	28-Sep-81	3
7844	TURNER    SALESMAN  7698	8-Sep-81	3
7900	JAMES     CLERK     7698	3-Dec-81	3
7782	CLARK     MANAGER   7839	9-Jun-81	2
7934	MILLER    CLERK     7782	23-Jan-82	3

If walking from bottom to top
Col_1 should be the child key and col_2 should be the parent key

CONNECT BY PRIOR mgr = empno

Using Level

LEVEL psedo column shows the level or rank of the particular row in the hierarchical tree. If you see the below query, It shows the level of KING and the level of the guys reporting directly to him

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       LEVEL 
FROM   emp 
WHERE  LEVEL <= 2 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

empno	ename	job	mgr	hiredate	level
7839	KING	PRESIDENT		17-Nov-81	1
7566	JONES	MANAGER	7839	2-Apr-81	2
7698	BLAKE	MANAGER	7839	1-May-81	2
7782	CLARK	MANAGER	7839	9-Jun-81	2

Here The level is used in the where clause to restrict the records till the second level.

Level also can be used to format the Output to form a graph structure

SELECT Lpad(ename,Length(ename) + LEVEL * 10 - 10,'-') 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

KING
----------JONES
--------------------SCOTT
------------------------------ADAMS
--------------------FORD
------------------------------SMITH
----------BLAKE
--------------------ALLEN
--------------------WARD
--------------------MARTIN
--------------------TURNER
--------------------JAMES
----------CLARK
--------------------MILLER

[h2]Pruning branches/children[h2]

There might be business requirements to partially retrieve a hierarchical tree and to prune branches.
If you do not want to do so, use the where condition to restrict the branch but process the child row

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
WHERE  ename <> 'JONES' 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr

This will restrict the value Jones in the result set but will still will retrieve Scott and Ford.

Please refer to the attached Picture to get a complete understanding.

To Restrict the value clark and its children, you should be adding the condition after the CONNECT BY

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr 
                 AND ename <> 'CLARK'