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.
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 START WITH mgr IS NULL 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)