Hierarchical queries v14

A hierarchical query is a type of query that returns the rows of the result set in a hierarchical order based upon data forming a parent-child relationship. A hierarchy is typically represented by an inverted tree structure. The tree is comprised of interconnected nodes. Each node may be connected to none, one, or multiple child nodes. Each node is connected to one parent node except for the top node which has no parent. This node is the root node. Each tree has exactly one root node. Nodes that don’t have any children are called leaf nodes. A tree always has at least one leaf node - e.g., the trivial case where the tree is comprised of a single node. In this case it is both the root and the leaf.

In a hierarchical query the rows of the result set represent the nodes of one or more trees.

Note

It is possible that a single, given row may appear in more than one tree and thus appear more than once in the result set.

The hierarchical relationship in a query is described by the CONNECT BY clause which forms the basis of the order in which rows are returned in the result set. The context of where the CONNECT BY clause and its associated optional clauses appear in the SELECT command is shown below.

SELECT <select_list> FROM <table_expression> [ WHERE ...]
  [ START WITH <start_expression> ]
    { CONNECT BY <condition> }
  [ ORDER SIBLINGS BY <column1> [ ASC | DESC ]
      [, <column2> [ ASC | DESC ] ] ...
  [ GROUP BY ...]
  [ HAVING ...]
  [ <other> ...]

select_list is one or more expressions that comprise the fields of the result set. table_expression is one or more tables or views from which the rows of the result set originate. other is any additional legal SELECT command clauses.

CONNECT BY specifies the relationship between a parent record and its child records of the hierarchy.

  • The PRIOR keyword can be specified with only one expression within a condition in the CONNECT BY clause. It must be specified with either right or left expression to refer to a parent row in a hierarchical query. For example:

    PRIOR expr = expr
    or
    expr = PRIOR expr
  • If the CONNECT BY condition is compound, only one condition requires the PRIOR keyword, although there can be multiple PRIOR conditions.

  • PRIOR keyword can be used to evaluate the parent row in a parent-child relationship in a hierarchical query.

  • PRIOR keyword cannot be used with unary expressions.

  • PRIOR keyword can be used to add a pseudo-column for each record corresponding to an expression in a target list. The pseudo-column represents a parent record's actual value when referenced in a child query using PRIOR expression.

You can further enhance the functionality of the CONNECT BY [PRIOR] condition by using the CONNECT_BY_ROOT operator. This operator returns not only immediate parent rows but also all ancestors rows in the hierarchy. For more information, see Retrieving the Root Node with CONNECT_BY_ROOT.

Examples

The following example uses the CONNECT BY clause with PRIOR to define a relationship between employees and managers. Using the LEVEL value, the employee names are indented to further emphasize the depth in the hierarchy of each row.

SELECT LPAD(ename,length(ename)+2 *(LEVEL - 1),' ') AS employee, empno, mgr, LEVEL 
FROM emp 
CONNECT BY PRIOR empno = mgr AND PRIOR emp.deptno = emp.deptno;

The output from this query follows.

employee    | empno | mgr  | level 
------------+-------+------+-------
 SMITH      |  7369 | 7902 |     1
 ALLEN      |  7499 | 7698 |     1
 WARD       |  7521 | 7698 |     1
 JONES      |  7566 | 7839 |     1
   FORD     |  7902 | 7566 |     2
     SMITH  |  7369 | 7902 |     3
   SCOTT    |  7788 | 7566 |     2
     ADAMS  |  7876 | 7788 |     3
 MARTIN     |  7654 | 7698 |     1
 BLAKE      |  7698 | 7839 |     1
   WARD     |  7521 | 7698 |     2
   TURNER   |  7844 | 7698 |     2
   MARTIN   |  7654 | 7698 |     2
   JAMES    |  7900 | 7698 |     2
   ALLEN    |  7499 | 7698 |     2
 CLARK      |  7782 | 7839 |     1
   MILLER   |  7934 | 7782 |     2
 SCOTT      |  7788 | 7566 |     1
   ADAMS    |  7876 | 7788 |     2
 KING       |  7839 |      |     1
   CLARK    |  7782 | 7839 |     2
     MILLER |  7934 | 7782 |     3
 TURNER     |  7844 | 7698 |     1
 ADAMS      |  7876 | 7788 |     1
 JAMES      |  7900 | 7698 |     1
 FORD       |  7902 | 7566 |     1
   SMITH    |  7369 | 7902 |     2
 MILLER     |  7934 | 7782 |     1
(28 rows)

The following example adds the ORDER BY clause and a compound condition with AND operator in CONNECT BY clause to show employee and manager hierarchy.

SELECT LPAD(ename,length(ename)+2 *(LEVEL - 1),' ') AS employee, empno, mgr, LEVEL
FROM emp
CONNECT BY PRIOR empno = mgr AND LEVEL <= 3
ORDER BY LEVEL, ename;

The output from this query follows.

employee    | empno | mgr  | level 
------------+-------+------+-------
 ADAMS      |  7876 | 7788 |     1
 ALLEN      |  7499 | 7698 |     1
 BLAKE      |  7698 | 7839 |     1
 CLARK      |  7782 | 7839 |     1
 FORD       |  7902 | 7566 |     1
 JAMES      |  7900 | 7698 |     1
 JONES      |  7566 | 7839 |     1
 KING       |  7839 |      |     1
 MARTIN     |  7654 | 7698 |     1
 MILLER     |  7934 | 7782 |     1
 SCOTT      |  7788 | 7566 |     1
 SMITH      |  7369 | 7902 |     1
 TURNER     |  7844 | 7698 |     1
 WARD       |  7521 | 7698 |     1
   ADAMS    |  7876 | 7788 |     2
   ALLEN    |  7499 | 7698 |     2
   BLAKE    |  7698 | 7839 |     2
   CLARK    |  7782 | 7839 |     2
   FORD     |  7902 | 7566 |     2
   JAMES    |  7900 | 7698 |     2
   JONES    |  7566 | 7839 |     2
   MARTIN   |  7654 | 7698 |     2
   MILLER   |  7934 | 7782 |     2
   SCOTT    |  7788 | 7566 |     2
   SMITH    |  7369 | 7902 |     2
   TURNER   |  7844 | 7698 |     2
   WARD     |  7521 | 7698 |     2
     ADAMS  |  7876 | 7788 |     3
     ALLEN  |  7499 | 7698 |     3
     FORD   |  7902 | 7566 |     3
     JAMES  |  7900 | 7698 |     3
     MARTIN |  7654 | 7698 |     3
     MILLER |  7934 | 7782 |     3
     SCOTT  |  7788 | 7566 |     3
     SMITH  |  7369 | 7902 |     3
     TURNER |  7844 | 7698 |     3
     WARD   |  7521 | 7698 |     3
(37 rows)

The following example uses the PRIOR keyword to return the employee name and manager name for each employee.

SELECT ename, PRIOR ename AS mgr FROM emp START WITH mgr 
IS NULL CONNECT BY PRIOR empno = mgr ORDER BY ename;

The output from this query follows.

ename   |  mgr  
--------+-------
 ADAMS  | SCOTT
 ALLEN  | BLAKE
 BLAKE  | KING
 CLARK  | KING
 FORD   | JONES
 JAMES  | BLAKE
 JONES  | KING
 KING   | 
 MARTIN | BLAKE
 MILLER | CLARK
 SCOTT  | JONES
 SMITH  | FORD
 TURNER | BLAKE
 WARD   | BLAKE
(14 rows)

The following example uses CONNECT BY clause with an optional PRIOR statement to show the level value.

SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 5;

The output from this query follows.

level 
--------
     1
     2
     3
     4
     5
(5 rows)

The clauses pertinent to hierarchical queries, START WITH, CONNECT BY, and ORDER SIBLINGS BY are described in the following sections.

defining_the_parent_child_relationship selecting_the_root_nodes organization_tree_in_the_sample_application node_level ordering_the_siblings retrieving_the_root_node_with_connect_by_root retrieving_a_path_with_sys_connect_by_path