The START WITH… CONNECT BY clause can be used to select data that has a hierarchical relationship such as manager to employee relationships. The root of the hierarchy is selected with the START WITH condition. In the example below from Oracle, the top level manager has an employee_id of 100. The output is sorted by the employee_id starting with 100. Manager/Employee relationships are established using the CONNECT BY and PRIOR conditions. The example below connects employees to their manager by finding the next manager_id by locating a row that is equal to the PRIOR row’s employee_id value.

The SIBLINGS key word in the ORDER BY clause preserves ordering within the hierarchy.

SELECT employee_name, employee_id, manager_id, level
FROM tbl_employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3