Table of Contents
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;
![HIERARCHICAL QUERIES : output](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-1024x80.png)
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;
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-1-1024x305.png)
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;
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-2.png)
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;
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-3.png)
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.
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-4.png)
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';
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-6.png)
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;
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-7.png)
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;
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-8.png)
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;
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-9.png)
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;
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-10.png)
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;
![](https://sayantanblogonoracle.in/wp-content/uploads/2023/09/image-11.png)
RELATED TOPICS: