Hierarchical Queries (CONNECT BY)

START WITH sets the root rows. CONNECT BY PRIOR defines the parent-child relationship (the direction decides top-down vs bottom-up).

Pseudocolumns: LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE.

Operators: CONNECT_BY_ROOT, PRIOR, SYS_CONNECT_BY_PATH.

Filtering: WHERE is applied after the hierarchy is built. A condition in CONNECT BY prunes branches.

SELECT LEVEL, LPAD(' ', 2 * (LEVEL - 1)) || last_name AS name,
       SYS_CONNECT_BY_PATH(last_name, '/') AS path,
       CONNECT_BY_ROOT last_name AS root,
       CONNECT_BY_ISLEAF AS is_leaf,
       PRIOR last_name AS manager
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;