Understanding Hierarchy
Before starting with Oracle Hierarchical queries and how Oracle handles and operates with hierarchical data, let’s understand what it means by hierarchy.
Below image explains a simple hierarchy and shows how tabular data can be represented in form of a tree structure.
SQL*PLUS editor or tools like SQL Developer or Toad are COI tools (Character Oriented Interface). Unlike GUI tools, COI tools do not display records in the form of hierarchical tree structure. Oracle SQL*PLUS editor shows the hierarchical data in the form of tabular records from which you have to analyze the hierarchy.
Having known graphical tree representation of tabular hierarchical data, let us now represent EMP table into hierarchical form.
EMP TABLE HIERARCHY
SELECT e.Empno EID,
e.Ename,
coalesce(to_char(m.Mgr), 'NA') MID,
coalesce(m.Ename, 'BOSS') MNAME
FROM Emp e
LEFT OUTER JOIN Emp m ON e.Mgr = m.Empno
ORDER BY 4;
EID ENAME MID MNAME
---------- ---------- -------- --------------
7521 WARD 7839 BLAKE
7499 ALLEN 7839 BLAKE
7654 MARTIN 7839 BLAKE
7844 TURNER 7839 BLAKE
7900 JAMES 7839 BLAKE
7839 KING NA BOSS
7934 MILLER 7839 CLARK
7369 SMITH 7566 FORD
7788 SCOTT 7839 JONES
7902 FORD 7839 JONES
7782 CLARK NA KING
7698 BLAKE NA KING
7566 JONES NA KING
7876 ADAMS 7566 SCOTT
14 rows selected.
EMP TABLE GRAPHICAL REPRESENTATION
Using hierarchical data
Hierarchical queries in Oracle help you to operate on hierarchical data in a table. There is one simple way to identify the data in a table is hierarchical or not.
- If a table contains self relation, then you can say there is a hierarchy between the Primary Key and Foreign Key columns of the table.
Hierarchical queries are also called Recursive queries as they internally use Recursion.
Oracle provides following clauses to deal with hierarchical data in a table.
START WITH: It is used to specify the root row of the hierarchy. Root may be single row or multiple rows, decided based on the condition provided.
CONNECT BY: It is used to specify the relationship between the parent rows and the child rows of the hierarchy. The relationship is specified through a condition.
PRIOR: One expression in CONNECT BY condition must be qualified with the PRIOR operator to refer to the parent row. It evaluates the immediate parent row(s) of the current row in a hierarchical query.
Steps followed by Oracle in Hierarchical query execution process.
- A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
- Oracle then evaluates the hierarchy as follows. Oracle selects the root row(s) of the hierarchy, which satisfy the condition of the START WITH clause.
- Then Oracle selects the child rows of each root row. Each child row is selected based on the condition supplied in the CONNECT BY clause, with respect to one of the root rows.
- Oracle then selects the children for the children row(s) identified in above step, and then the children of those children, and so on.
- If the query contains a WHERE clause, Oracle removes all rows from the hierarchy that do not satisfy the condition of the WHERE clause.
- Oracle returns the rows in the order where each children appear below their parents.
- Learn the execution order of hierarchical queries with respect to other clauses here.
--Get the complete hierarchy of employees working under KING SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Ename = 'KING' CONNECT BY PRIOR Empno = Mgr; ENAME EMPNO MGR JOB ---------- ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK 14 rows selected.
You can use any column and any operator in the START WITH clause that can mark the root node or nodes.
--Get the complete hierarchy of employees working under organization PRESIDENT SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Job = 'PRESIDENT' CONNECT BY PRIOR Empno = Mgr; ENAME EMPNO MGR JOB ---------- ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK 14 rows selected.
--Get the complete hierarchy of employees working under BLAKE SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Ename = 'BLAKE' CONNECT BY PRIOR Empno = Mgr; ENAME EMPNO MGR JOB ---------- ---------- ---------- --------- BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK 6 rows selected.
--Get the complete hierarchy of employees working under Employee ID-7566 SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Empno = 7566 CONNECT BY PRIOR Empno = Mgr; ENAME EMPNO MGR JOB ---------- ---------- ---------- --------- JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK
When multiple rows acts as root in hierarchy, Oracle recursively finds all child for each root.
--Get the complete hierarchy of employees working under employee who earns 3000 SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Sal = 3000 CONNECT BY PRIOR Empno = Mgr; ENAME EMPNO MGR JOB ---------- ---------- ---------- --------- SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK
--Get the complete hierarchy of employees working under all managers SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Job = 'MANAGER' CONNECT BY PRIOR Empno = Mgr; ENAME EMPNO MGR JOB ---------- ---------- ---------- --------- JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK 13 rows selected.
START WITH and CONNECT BY can accept expressions. Subqueries to any level can be used in START WITH clause.
--Get the complete hierarchy of employees working under the highest paid employee SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Sal = ( SELECT max(Sal) FROM Emp ) CONNECT BY PRIOR Empno = Mgr; ENAME EMPNO MGR JOB ---------- ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK 14 rows selected.
Q : Get the hierarchy of employees for the highest salary earner in RESEARCH department?
Ans:
SELECT Ename, Empno, Mgr, Job, Sal
FROM Emp
START WITH Sal=(
SELECT MAX(Sal)
FROM Emp
WHERE Deptno = (
SELECT Deptno
FROM Dept
WHERE Dname = 'RESEARCH'
)
)
CONNECT BY PRIOR Empno = Mgr;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
If you place PRIOR on the opposite side, then the hierarchy is calculated from bottom to top that means from child to parent.
Q : Get all the parents for “ALLEN”?
Ans:
SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Ename = 'ALLEN' CONNECT BY Empno = PRIOR Mgr; ENAME EMPNO MGR JOB ---------- ---------- ---------- --------- ALLEN 7499 7698 SALESMAN BLAKE 7698 7839 MANAGER KING 7839 PRESIDENT
Q : Get all the top level employees of ANALYST?
Ans:
SELECT Ename, Empno, Mgr, Job, Sal FROM Emp START WITH Job = 'ANALYST' CONNECT BY Empno = PRIOR Mgr; ENAME EMPNO MGR JOB SAL ---------- ---------- ---------- --------- ---------- SCOTT 7788 7566 ANALYST 3000 JONES 7566 7839 MANAGER 2975 KING 7839 PRESIDENT 5000 FORD 7902 7566 ANALYST 3000 JONES 7566 7839 MANAGER 2975 KING 7839 PRESIDENT 5000 6 rows selected.
Hierarchical queries can’t be used to perform Joins. That means they can’t join columns from different tables in the CONNECT BY clause. But hierarchical queries can contain Joins.
They can’t select data from a view, whose query performs a join.
--Get the complete hierarchy of employees when each employees in department 10 is the parent SELECT E.Ename, E.Sal, E.Empno, E.Mgr, D.Deptno, D.Dname FROM Emp E, Dept D WHERE E.Deptno = D.Deptno START WITH D.Deptno = 10 CONNECT BY PRIOR Empno = Mgr; ENAME SAL EMPNO MGR DEPTNO DNAME ---------- ---------- ---------- ---------- ---------- ------------- MILLER 1300 7934 7782 10 ACCOUNTING CLARK 2450 7782 7839 10 ACCOUNTING MILLER 1300 7934 7782 10 ACCOUNTING KING 5000 7839 10 ACCOUNTING JONES 2975 7566 7839 20 RESEARCH SCOTT 3000 7788 7566 20 RESEARCH ADAMS 1100 7876 7788 20 RESEARCH FORD 3000 7902 7566 20 RESEARCH SMITH 800 7369 7902 20 RESEARCH CLARK 2450 7782 7839 10 ACCOUNTING MILLER 1300 7934 7782 10 ACCOUNTING BLAKE 2850 7698 7839 30 SALES ALLEN 1600 7499 7698 30 SALES WARD 1250 7521 7698 30 SALES MARTIN 1250 7654 7698 30 SALES TURNER 1500 7844 7698 30 SALES JAMES 950 7900 7698 30 SALES 17 rows selected.
--Get the complete hierarchy of employees working under grade-5 employees SELECT E.Ename, E.Sal, E.Empno, E.Mgr, D.Deptno, D.Dname, SG.Grade FROM Emp E, Dept D, Salgrade SG WHERE E.Deptno = D.Deptno AND E.Sal BETWEEN SG.LoSal AND SG.HiSal START WITH SG.Grade = 5 CONNECT BY PRIOR Empno = Mgr; ENAME SAL EMPNO MGR DEPTNO DNAME GRADE ---------- ---------- ---------- ---------- ---------- ----------- -------- KING 5000 7839 10 ACCOUNTING 5 JONES 2975 7566 7839 20 RESEARCH 4 SCOTT 3000 7788 7566 20 RESEARCH 4 ADAMS 1100 7876 7788 20 RESEARCH 1 FORD 3000 7902 7566 20 RESEARCH 4 SMITH 800 7369 7902 20 RESEARCH 1 BLAKE 2850 7698 7839 30 SALES 4 ALLEN 1600 7499 7698 30 SALES 3 WARD 1250 7521 7698 30 SALES 2 MARTIN 1250 7654 7698 30 SALES 2 TURNER 1500 7844 7698 30 SALES 3 JAMES 950 7900 7698 30 SALES 1 CLARK 2450 7782 7839 10 ACCOUNTING 4 MILLER 1300 7934 7782 10 ACCOUNTING 2 14 rows selected.
CONNECT BY clause can contain filter condition which is evaluated to find the immediate child records. Whereas WHERE clause filter is applied after hierarchical query is processed.
--WHERE clause is applied after CONNECT BY is evaluated SELECT Ename, Empno, Mgr, Job, Sal FROM Emp WHERE Job = 'MANAGER' START WITH Ename = 'KING' CONNECT BY PRIOR Empno = Mgr; ENAME EMPNO MGR JOB SAL ---------- ---------- ---------- --------- ---------- JONES 7566 7839 MANAGER 2975 CLARK 7782 7839 MANAGER 2450 BLAKE 7698 7839 MANAGER 2850
--Condition in SELECT EName, Empno, Mgr, Job, Sal FROM Emp START WITH Ename = 'KING' CONNECT BY PRIOR Empno = Mgr AND Job = 'MANAGER'; ENAME EMPNO MGR JOB SAL ---------- ---------- ---------- --------- ---------- KING 7839 PRESIDENT 5000 JONES 7566 7839 MANAGER 2975 CLARK 7782 7839 MANAGER 2450 BLAKE 7698 7839 MANAGER 2850
SELECT EName, Empno, Mgr, Job, Sal FROM Emp START WITH Ename = 'KING' CONNECT BY PRIOR Empno = Mgr AND Job IN( 'CLERK', 'ANALYST'); ENAME EMPNO MGR JOB SAL ---------- ---------- ---------- --------- ---------- KING 7839 PRESIDENT 5000
SELECT EName, Empno, Mgr, Job, Sal
FROM Emp
WHERE Job IN('PRESIDENT', 'CLERK')
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr AND Job = 'MANAGER';
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
CONNECT BY exists without a START WITH clause but not the reverse. If START WITH is present, there must be a CONNECT BY.
--Error! START WITH without CONNECT BY gives error
SELECT Ename, Empno, Mgr, Job
FROM Emp
START WITH Sal = 5000;
FROM Emp
*
ERROR at line 2:
ORA-01788: CONNECT BY clause required in this query block
--CONNECT BY without START WITH works fine SELECT Ename, Empno, Mgr, Job, Sal FROM Emp CONNECT BY PRIOR Empno = Mgr AND Job = 'ANALYST'; ENAME EMPNO MGR JOB SAL ---------- ---------- -------- --------- ---------- SCOTT 7788 7566 ANALYST 3000 FORD 7902 7566 ANALYST 3000 ALLEN 7499 7698 SALESMAN 1600 JAMES 7900 7698 CLERK 950 TURNER 7844 7698 SALESMAN 1500 MARTIN 7654 7698 SALESMAN 1250 WARD 7521 7698 SALESMAN 1250 MILLER 7934 7782 CLERK 1300 ADAMS 7876 7788 CLERK 1100 BLAKE 7698 7839 MANAGER 2850 JONES 7566 7839 MANAGER 2975 SCOTT 7788 7566 ANALYST 3000 FORD 7902 7566 ANALYST 3000 CLARK 7782 7839 MANAGER 2450 SMITH 7369 7902 CLERK 800 KING 7839 PRESIDENT 5000 16 rows selected.
ORDER BY clause is executed last and and disturbs the result set hierarchy order. In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will destroy the hierarchical order of the CONNECT BY results.
SELECT Ename, Empno, Mgr, Job FROM Emp START WITH Mgr IS NULL CONNECT BY PRIOR Empno = Mgr ORDER BY 1; ENAME EMPNO MGR JOB ---------- ---------- ------- --------- ADAMS 7876 7788 CLERK ALLEN 7499 7698 SALESMAN BLAKE 7698 7839 MANAGER CLARK 7782 7839 MANAGER FORD 7902 7566 ANALYST JAMES 7900 7698 CLERK JONES 7566 7839 MANAGER KING 7839 PRESIDENT MARTIN 7654 7698 SALESMAN MILLER 7934 7782 CLERK SCOTT 7788 7566 ANALYST SMITH 7369 7902 CLERK TURNER 7844 7698 SALESMAN WARD 7521 7698 SALESMAN 14 rows selected.
If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.
Hierarchical Functions and Pseudocolumns
Oracle hierarchical queries come with pseudo columns, functions and clauses to help make sense of the hierarchy.
- Functions
- CONNECT_BY_ROOT : Returns the root node(s) associated with the current row.
- SYS_CONNECT_BY_PATH : Returns a delimited breadcrumb from root to the current row.
- Pseudo Columns
- LEVEL : The position in the hierarchy of the current row in relation to the root node.
- CONNECT_BY_ISLEAF : Indicates if the current row is a leaf node.
- CONNECT_BY_ISCYCLE :
- Clause
- ORDER SIBLINGS BY : Applies an order to siblings, without altering the basic hierarchical structure of the data returned by the query.
Learn about functions available that ease working with hierarchical queries in next chapter.