Organization Tree in the Sample Application v13

Consider the emp table of the sample application. The rows of the emp table form a hierarchy based upon the mgr column which contains the employee number of the employee’s manager. Each employee has at most, one manager. KING is the president of the company so he has no manager, therefore KING’s mgr column is null. Also, it is possible for an employee to act as a manager for more than one employee. This relationship forms a typical, tree-structured, hierarchical organization chart as illustrated below.

To form a hierarchical query based upon this relationship, the SELECT command includes the clause, CONNECT BY PRIOR empno = mgr. For example, given the company president, KING, with employee number 7839, any employee whose mgr column is 7839 reports directly to KING which is true for JONES, BLAKE, and CLARK (these are the child nodes of KING). Similarly, for employee, JONES, any other employee with mgr column equal to 7566 is a child node of JONES - these are SCOTT and FORD in this example.

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

The complete SELECT command is shown below.

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 in a top-to-bottom, left-to-right order. Below is the output from 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)