Hierarchical SQL

This post reviews several ways to write Oracle SQL to reflect a hierarchical structure in an organization. The caveat here is that the table you are querying must be mapped with hierarchical data.

In the example below, the SQL statement is querying a table with Employee ID's and their respective Managers. The CONNECT BY clause denotes the parent rows and the child rows of the hierarchy.

SELECT emp_id, l_name, mgr_id
FROM employees
CONNECT BY PRIOR emp_id = mgr_id;

This query produces the result set that defines the relationship between employees and managers.

EMP_ID	L_NAME	MGR_ID
101	Brown	100
108	Greenberg	101
109	Jones	108

In this next example, the LEVEL psuedocolumn shows the parent and child rows.

SELECT emp_id, l_name, mgr_id, LEVEL
FROM employees
CONNECT BY PRIOR emp_id = mgr_id;

The result set looks like this:

EMP_ID	L_NAME	MGR_ID	LEVEL
101	Koch	100	1
108	Greenberg	101	2
109	Franks	108	3

In this last example, the query includes the START WITH clause to specify a root row to start the hierarchy. In addition, the Oracle keyword, SIBLINGS, used in the ORDER BY clause preserves the order within the hierarchy.

SELECT l_name, emp_id, mgr_id, LEVEL
FROM employees
START WITH emp_id = 100
CONNECT BY PRIOR emp_id = mgr_id
ORDER SIBLINGS BY l_name;