The following example shows the emp table of the sample application. The rows of the emp table form a hierarchy based on the mgr column. This column contains the employee number of the manager of the employee. Each employee has up to one manager. KING is the president of the company so that he has no manager. The mgr column of KING is null. An employee may act as a manager for more than one employee. This relationship forms a typical, tree-structured, hierarchical organization chart.

The following figure shows this relationship.

Employee organization hierarchy

To form a hierarchical query based on this relationship, the SELECT statement includes this clause: CONNECT BY PRIOR empno = mgr. For example, if the company president KING has the employee number 7839, any employee whose mgr column is 7839 reports to KING. In this case, JONES, BLAKE, and CLARK are the qualified employees, because they are the child nodes of KING. Similarly, for the employee JONES, any other employee with the mgr column that matches 7566 is a child node of JONES. The qualified employees are SCOTT and FORD in this example.

The top of the organization chart is KING so that there is one root node in this tree. The START WITH mgr IS NULL clause only selects KING as the initial root node.

The following example shows the complete SELECT statement:

SELECT ename, empno, mgr
FROM emp
CONNECT BY PRIOR empno = mgr;

The rows in the query output traverse each branch from the root to leaf moving from top to bottom and from left to right. The following example shows the output of this query:

 ename  | empno | mgr
 KING   |  7839 |
 JONES  |  7566 | 7839
 SCOTT  |  7788 | 7566
 ADAMS  |  7876 | 7788
 FORD   |  7902 | 7566
 SMITH  |  7369 | 7902
 BLAKE  |  7698 | 7839
 ALLEN  |  7499 | 7698
 WARD   |  7521 | 7698
 MARTIN |  7654 | 7698
 TURNER |  7844 | 7698
 JAMES  |  7900 | 7698
 CLARK  |  7782 | 7839
 MILLER |  7934 | 7782
(14 rows)