Hierarchical Query

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;

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