Mastering Oracle SQL

Oracle SQL Extensions

In the last few examples, we saw how we can perform some operations on the hierarchical tree by using simple SQL techniques. Operations such as traversing a tree, finding levels, etc., require more complex SQL statements, and also require the use of features designed specifically for working with hierarchical data. Oracle provides some extensions to ANSI SQL to facilitate these operations. But before moving to the Oracle SQL extensions, let’s look at how we can traverse a tree using ANSI SQL, and at the problems we’ll encounter when doing that.

For example, let’s say we want to list each employee with his manager. Using regular Oracle SQL, we can perform self outer joins on the EMPLOYEE table, as shown here:

            SELECT E_TOP.LNAME, E_2.LNAME, E_3.LNAME, E_4.LNAME
            FROM EMPLOYEE E_TOP, EMPLOYEE E_2, EMPLOYEE E_3, EMPLOYEE E_4
            WHERE E_TOP.MANAGER_EMP_ID IS NULL
            AND E_TOP.EMP_ID = E_2.MANAGER_EMP_ID (+)
            AND E_2.EMP_ID = E_3.MANAGER_EMP_ID (+)
            AND E_3.EMP_ID = E_4.MANAGER_EMP_ID (+);

LNAME      LNAME      LNAME      LNAME   
---------- ---------- ---------- ----------
KING       BLAKE      ALLEN
KING       BLAKE      WARD
KING       BLAKE      MARTIN
KING       JONES      SCOTT      ADAMS
KING       BLAKE      TURNER
KING       BLAKE      JAMES
KING       JONES      FORD       SMITH
KING       CLARK      MILLER

8 rows selected.

The query returns eight rows, corresponding to the eight branches of the tree. To get those results, the query performs a self join on four instances of the EMPLOYEE table. Four EMPLOYEE table instances are needed in this statement because there are four ...