Hierarchical queries v15

Suggest edits

A hierarchical query is a type of query that returns the rows of the result set in a hierarchical order based on data forming a parent-child relationship. A hierarchy is typically represented by an inverted tree structure. The tree is made up of interconnected nodes. Each node can 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, for example, the trivial case where the tree is made up of a single node. In this case, the node 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

A single given row can appear in more than one tree and thus appear more than once in the result set.

Syntax

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. This example shows the context of where the CONNECT BY clause and its associated optional clauses appear in the SELECT command:

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 make up 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.

  • You can specify the PRIOR keyword with only one expression in a condition in the CONNECT BY clause. You must specify it 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.

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

  • You can't use PRIOR keyword with unary expressions.

  • You can use the PRIOR keyword 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 a PRIOR expression.

Use the CONNECT_BY_ROOT operator to further enhance the functionality of the CONNECT BY [PRIOR] condition. 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

This 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 is:

Output
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)
This example adds the ORDER BY clause and a compound condition with AND operator in a 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 is:
Output
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)
This 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 is:
Output
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)
This example uses a 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 is:
Output
level 
--------
     1
     2
     3
     4
     5
(5 rows)
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
← Prev
Synonyms
↑ Up
Advanced concepts
Next →
Defining the parent/child relationship