Sayantan's Blog On Oracle

HIERARCHICAL QUERIES

If any table contains hierarchical data then we can select hierarchical rows by using hierarchical queries.

Syntax of Hierarchical queries:

SELECT LEVEL, COLUMN, EXPRESSION..
FROM TABLE
[WHERE CONDITION(S)]
[START WITH CONDITION(S)]
[CONNECT BY PRIOR CONDITION(S)]

KEYWORD	DESCRIPTION
SELECT	Normal SELECT statement
LEVEL	In a hierarchical query, the LEVEL pseudocolumn returns 1 for each root row, 2 for child of the root and so on.
FROM TABLE	Specifies the table, view or mview containing the column.
WEHRE	Restricts the rows returned by the query without effecting other rows of the hierarchy
CONDITION	Is a comparison with expression
START WITH	Specifies the root rows of the hierarchy (where to start)
CONNECT BY PRIOR	Specifies the column in which the relationship between parent and child rows exist. This clause is required for hierarchical queries.

BOTTOM UP APPROACH:

SELECT * FROM EMPLOYEES
START WITH EMPLOYEE_ID = 101
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID;

In the above example, we can see that King is at the bottom of the data set. Then the next level manager is coming. That's why it is called the BOTTOM UP approach of traversing the tree.

TOP DOWN APPROACH:

SELECT * FROM EMPLOYEES
START WITH EMPLOYEE_ID = 101
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

In the above example we can see that the Employee Kochhar is at the top of the list. Then the next level employee's data are coming. That's why it is called the TOP DOWN approach of traversing the tree.

SELECT LAST_NAME||' REPORTS TO '||PRIOR LAST_NAME "TOP DOWN"
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

SELECT LPAD(LAST_NAME, LENGTH(LAST_NAME) + (LEVEL * 2) -2, ' ') ORG_CHART
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

SELECT LPAD(LAST_NAME, LENGTH(LAST_NAME) + (LEVEL * 2) -2, ' ') ORG_CHART
FROM EMPLOYEES
WHERE LAST_NAME <> 'Greenberg'
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

If we compare the output with the previous output, we can see that the manager Greenberg is not visible in above output. But the hierarchy of Greenberg is present.

If we want to exclude the entire hierarchy of Greenberg then we will use the below query.

SELECT LPAD(LAST_NAME, LENGTH(LAST_NAME) + (LEVEL * 2) -2, ' ') ORG_CHART
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
AND LAST_NAME <> 'Greenberg';

Now we can see that the entire hierarchy of Greenberg has been removed from output.

MORE EXAMPLES:

Extract the employee list along with their respective managers.

SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, (SELECT LAST_NAME
                                            FROM EMPLOYEES
                                            WHERE EMPLOYEE_ID = A.MANAGER_ID) MANAGER_NAME
FROM EMPLOYEES A
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

Identifying hierarchy with LEVEL key word.

SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, (SELECT LAST_NAME
                                            FROM EMPLOYEES
                                            WHERE EMPLOYEE_ID = A.MANAGER_ID) MANAGER_NAME, LEVEL
FROM EMPLOYEES A
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

SIBLINGS BY KEY WORD:

SIBLINGS BY keyword is used to maintain the ordering with in the hierarchy.

SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, (SELECT LAST_NAME
                                            FROM EMPLOYEES
                                            WHERE EMPLOYEE_ID = A.MANAGER_ID) MANAGER_NAME, LEVEL
FROM EMPLOYEES A
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY LAST_NAME;

SYS_CONNECT_BY_PATH

It is used to show the employees hierarchy path.

SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, (SELECT LAST_NAME
                                            FROM EMPLOYEES
                                            WHERE EMPLOYEE_ID = A.MANAGER_ID) MANAGER_NAME, 
       LEVEL, SYS_CONNECT_BY_PATH(LAST_NAME, '/') EMP_HIERARCHY
                                            
FROM EMPLOYEES A
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY LAST_NAME;

SYS_CONNECT_BY_ROOT

It is used to find the root manager.

SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID, (SELECT LAST_NAME
                                            FROM EMPLOYEES
                                            WHERE EMPLOYEE_ID = A.MANAGER_ID) MANAGER_NAME, 
       LEVEL, 
       CONNECT_BY_ROOT LAST_NAME ROOT_MANAGER,
       SYS_CONNECT_BY_PATH(LAST_NAME, '/') EMP_HIERARCHY
                                            
FROM EMPLOYEES A
WHERE LEVEL > 1 AND DEPARTMENT_ID = 110
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY LAST_NAME;

RELATED TOPICS:

SUBQUERIES IN ORACLE: