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;