Mastering Oracle SQL

Simple Hierarchy Operations

The processes for extracting some types of information from a table storing hierarchical data are relatively simple, and can be performed using the techniques that we have discussed so far in this book. Extracting more complex information requires using some new SQL constructs, which we’ll discuss in the later section titled Section 8.3. In this section, we’ll discuss the hierarchy operations that can be performed using what we’ve learned so far.

Finding the Root Node

Finding the root of a hierarchy tree is easy; we look for the one node with no parent. In the EMPLOYEE table we discussed earlier, the value for MANAGER_EMP_ID is NULL for the uppermost employee, and only for the uppermost employee. The following query searches for cases where MANAGER_EMP_ID is NULL, thereby returning the root node:

               SELECT EMP_ID, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE 
               FROM EMPLOYEE 
               WHERE MANAGER_EMP_ID IS NULL;

   EMP_ID LNAME        DEPT_ID MANAGER_EMP_ID    SALARY HIRE_DATE 
--------- ---------- --------- -------------- --------- --------- 
     7839 KING              10                     5000 17-NOV-81

Because the MANAGER_EMP_ID column defines the hierarchy, it’s important that it always contain correct data. While populating data in this table, we must make sure to specify a MANAGER_EMP_ID for every row other than the row for the uppermost employee. The uppermost employee doesn’t report to anyone (doesn’t have a manager), and hence MANAGER_EMP_ID is not applicable for him. If we leave out MANAGER_EMP_ID ...