🌳 Hierarchical Queries
CONNECT BY and recursive CTEs for org trees and BOMs.
| Snippet | |
|---|---|
| ☆CONNECT BY — Walk an org tree top-down |
SELECT empno, name, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR empno = manager_id;
|
| ☆ORDER SIBLINGS — Sort siblings without breaking the tree |
SELECT empno, name, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR empno = manager_id ORDER SIBLINGS BY name;
|
| ☆Recursive CTE — Standard-SQL alternative to CONNECT BY |
WITH org(empno, name, mgr, lvl) AS (SELECT empno, name, manager_id, 1 FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.empno, e.name, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.empno) SELECT * FROM org;
|
| ☆SYS_CONNECT_BY_PATH — Show the full path of each node |
SELECT SYS_CONNECT_BY_PATH(name, ' > ') AS path FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR empno = manager_id;
|
| ☆Leaf Nodes — Only nodes with no children |
SELECT name FROM employees WHERE CONNECT_BY_ISLEAF = 1 START WITH manager_id IS NULL CONNECT BY PRIOR empno = manager_id;
|