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