Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 8 Hierarchical Query
  5. 8.1 Working with hierarchical queries

8.1 Working with hierarchical queries

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.

Was this article helpful to you? Yes No

How can we help?