Hierarchical queries

Hierarchical query is a type of SQL query which is commonly used to get meaningful results from  hierarchical data.

Hierarchical data means table columns which are related to each other by hierarchical relationships.

Hierarchical Relationship exist when one column data is parent to another column data.


In Our real life, Below would be hierarchical relations.

1) Employee and Manager Hierarchy
2)  Organization Hierarchy

Below clauses we need to know to write Hierarchical queries.



START WITH: It specifies the root rows of the hierarchy.

Connect by: It specifies the relationship between parent rows and child rows of the hierarchy.

Prior: We use this keyword to specify parent row.

In Hierarchical queries,One expression in condition must be specified with PRIOR to refer Parent row.
eg:

… PRIOR expr = expr
or
… expr = PRIOR expr

Level: It refers to a pseudo column in Oracle which is used in a hierarchical query to identify the hierarchy level in numeric format.


A database evaluates hierarchical queries in the following order:

•     A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates

•     The CONNECT BY condition is evaluated

•     Any remaining WHERE clause predicates are evaluated

The database engine then uses the information from these evaluations to form the hierarchy using the following steps (referred from Oracle 10g documentation):

1) It selects the root row(s) of the hierarchy, meaning those rows that satisfy the START WITH condition.

2) It selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

3) It selects successive generations of child rows. The database engine first selects the children of the rows returned in step 2, and then the children of those children, and so on. The database always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

4) If the query contains a WHERE clause without a join, then the database eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. The database evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

5) The database returns the rows in the order shown in Figure 1 below. In the diagram, children appear below their parents.


lets take EMP table to write examples.

case1: If we want to know relation between empid and manager.

SQL> select e1.empno,e1.mgr manager from emp e1
  2  connect by mgr = prior empno
  3  start with mgr is null
  4  /
EMPNO MANAGER
----- -------
 7839
 7566    7839
 7788    7566
 7876    7788
 7902    7566
 7369    7902
 7698    7839
 7499    7698
 7521    7698
 7654    7698
 7844    7698
 7900    7698
 7782    7839
 7934    7782

Here we used start with mgr is null as we would like to see CEO empid as well but he does not report to anyone.

and same kind of output we can get with below query as well but here we will not have output in hierarchical relation. so  we can compare both above and below output to understand difference.

SQL> select e1.empno, e1.mgr from emp e1 left  join emp e2 on e2.empno= e1.mgr;
 
EMPNO   MGR
----- -----
 7839
 7698  7839
 7782  7839
 7566  7839
 7788  7566
 7902  7566
 7369  7902
 7499  7698
 7521  7698
 7654  7698
 7844  7698
 7876  7788
 7900  7698
 7934  7782

Case 2: We need to get level for each hierarchy.

SQL> select e1.empno,e1.mgr manager ,level, decode(level,1,'CEO',2,'Director',3,'Manager',4, 'Employee') from emp e1
  2  connect by mgr = prior empno
  3  start with mgr is null;


EMPNO MANAGER      LEVEL DECODE(LEVEL,1,'CEO',2,'DIRECT
----- ------- ---------- --------------------------------
 7839                  1 CEO
 7566    7839          2 Director
 7788    7566          3 Manager
 7876    7788          4 Employee
 7902    7566          3 Manager
 7369    7902          4 Employee
 7698    7839          2 Director
 7499    7698          3 Manager
 7521    7698          3 Manager
 7654    7698          3 Manager
 7844    7698          3 Manager
 7900    7698          3 Manager
 7782    7839          2 Director
 7934    7782          3 Manager

case 3: generate Sequential values.


SQL> select level from dual
  2  connect by level <=5
  3  ;
     LEVEL
----------
         1
         2
         3
         4
         5

You can change value 5 and generate up to whichever value you want.