Building tree structures with SQL

Hierarchical data is everywhere — organizational charts, bill of materials, category trees. Oracle's CONNECT BY clause lets you traverse these structures with pure SQL, no recursion needed.

Recipe #1 - Basic org chart query

The classic employee-manager hierarchy:

SELECT LEVEL,
       LPAD(' ', (LEVEL - 1) * 4) || ename AS employee,
       empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;

• START WITH defines the root(s) of the tree
• CONNECT BY PRIOR defines the parent-child relationship
• ORDER SIBLINGS BY sorts nodes at the same level
• LEVEL is a pseudocolumn indicating the depth

Recipe #2 - SYS_CONNECT_BY_PATH for full paths

Build the complete path from root to each node:

SELECT empno, ename,
       SYS_CONNECT_BY_PATH(ename, ' > ') AS full_path,
       LEVEL AS depth
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

Result: > KING > JONES > SCOTT

Recipe #3 - Finding leaf nodes and roots

Identify nodes with no children (leaves) or no parent (roots):

SELECT ename,
       CONNECT_BY_ISLEAF AS is_leaf,
       CONNECT_BY_ROOT ename AS root_name,
       LEVEL AS depth
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

Recipe #4 - Detect and break cycles

If your data has circular references, CONNECT BY will loop forever. Use NOCYCLE to prevent that:

SELECT ename,
       SYS_CONNECT_BY_PATH(ename, '/') AS path,
       CONNECT_BY_ISCYCLE AS is_cycle
FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE PRIOR empno = mgr;

Recipe #5 - Recursive WITH (Oracle 11g+)

The ANSI alternative to CONNECT BY uses recursive common table expressions:

WITH emp_tree (empno, ename, mgr, lvl, path) AS (
  -- Anchor: root nodes
  SELECT empno, ename, mgr, 1, ename
  FROM emp
  WHERE mgr IS NULL
  UNION ALL
  -- Recursive: children
  SELECT e.empno, e.ename, e.mgr, t.lvl + 1,
         t.path || ' > ' || e.ename
  FROM emp e
  JOIN emp_tree t ON e.mgr = t.empno
)
SELECT LPAD(' ', (lvl - 1) * 4) || ename AS employee,
       path
FROM emp_tree
ORDER BY path;

Both approaches work, but CONNECT BY is often more concise for simple hierarchies, while recursive WITH is more powerful for complex transformations.