Hierarchical Queries

A hierarchical query walks parent-child relationships within a single table — the classic example is an organisation chart, where each employee row references a manager_id that points to another row in the same table.

Oracle's CONNECT BY syntax is purpose-built for this and predates standard SQL's recursive CTEs by decades. Even today, on Oracle, CONNECT BY is usually more concise and faster than the equivalent recursive WITH clause.

The Tree You're Querying

Consider this slice of the HR employees table:

This forms a tree:

Steven (100)
├── Neena (101)
│   └── Nancy (108)
│       └── Daniel (109)
└── Lex (102)

A flat SELECT * FROM employees shows rows in no particular order. A hierarchical query lets you walk the tree from a starting point down (or up) through every level.

The CONNECT BY Anatomy

SELECT employee_id, first_name, manager_id, LEVEL
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Result:

Three clauses are doing the work:

1. START WITH — the anchor. Identifies the root row(s) where the walk begins.
2. CONNECT BY — the recursive step. Tells Oracle how a child row relates to its parent.
3. PRIOR — points to the parent row when evaluating the next iteration.

Reading PRIOR

PRIOR column means "the value of column from the previous (parent) row in the walk".

CONNECT BY PRIOR employee_id = manager_id
--          └─ parent's emp_id     └─ child's manager_id

Read it as: "a row is a child of the prior row when the child's manager_id equals the parent's employee_id."

Reverse the equation to walk upward (from a leaf to the root):

START WITH employee_id = 109                 -- start at Daniel
CONNECT BY employee_id = PRIOR manager_id;   -- walk up to manager

LEVEL Pseudocolumn

LEVEL is automatically available in every hierarchical query. It tells you the depth of the current row, starting at 1 for the START WITH rows.

SELECT LPAD(' ', 2 * (LEVEL - 1)) || first_name AS org_chart,
       LEVEL
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Result:

LPAD(' ', 2*(LEVEL-1)) adds 2 spaces of indentation per level — a quick way to visualise a hierarchy in a flat result.

ORDER SIBLINGS BY

Plain ORDER BY would sort the whole result alphabetically and destroy the tree structure. ORDER SIBLINGS BY orders children within their parent group while keeping the hierarchical ordering intact.

SELECT LPAD(' ', 2 * (LEVEL - 1)) || first_name AS org_chart
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY first_name;

Now siblings appear alphabetically (Lex before Neena under Steven), but the parent-before-child structure is preserved.

CONNECT_BY_ROOT

The CONNECT_BY_ROOT operator returns a column value from the root of the current branch. Useful when you want each row to carry the name of the top-level manager.

SELECT first_name,
       LEVEL,
       CONNECT_BY_ROOT first_name AS top_manager
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Result:

In a multi-tree dataset (multiple root rows), each row gets the name of its own tree's root.

SYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH(column, separator) returns the full path from the root to the current row, with values joined by the separator.

SELECT first_name,
       LEVEL,
       SYS_CONNECT_BY_PATH(first_name, ' / ') AS path
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Result:

The separator must not appear in the column data, or the path is ambiguous.

CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF is 1 if the current row has no children in the walk and 0 otherwise.

SELECT first_name,
       CONNECT_BY_ISLEAF AS is_leaf
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Result:

Combined with a filter, you can find all "leaf" employees (the ones with no direct reports):

SELECT first_name
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
        AND CONNECT_BY_ISLEAF = 1;

Wait — putting CONNECT_BY_ISLEAF = 1 in CONNECT BY is wrong; it would stop the walk. Put it in WHERE:

SELECT first_name
FROM   employees
WHERE  CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Cycles and NOCYCLE

If the data accidentally contains a loop — A's manager is B, B's manager is C, C's manager is A — Oracle detects the cycle and aborts:

ORA-01436: CONNECT BY loop in user data

The NOCYCLE keyword tells Oracle to stop walking when a cycle is detected but not raise an error. CONNECT_BY_ISCYCLE then flags which rows participate in a cycle:

SELECT first_name,
       CONNECT_BY_ISCYCLE AS is_cycle_root
FROM   employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

CONNECT_BY_ISCYCLE = 1 marks the row where the walk hit a cycle and stopped.

Walking Up vs Walking Down

The direction of the walk depends on which side of the equation has PRIOR.

-- Walk DOWN from the CEO to everyone they manage:
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

-- Walk UP from Daniel to the CEO:
START WITH employee_id = 109
CONNECT BY employee_id = PRIOR manager_id;

Switching PRIOR from the left to the right of the equation flips the walk direction. Always double-check which side it's on when reading or debugging hierarchical SQL.

Filtering With WHERE

WHERE is applied after the hierarchy is built; it filters rows out of the final result without breaking the walk.

-- All employees under Steven, with salary > 8000:
SELECT first_name, salary, LEVEL
FROM   employees
WHERE  salary > 8000
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

Compare with adding the condition to CONNECT BY, which prunes the walk:

-- Walk stops at any branch where salary <= 8000:
SELECT first_name, salary, LEVEL
FROM   employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
       AND salary > 8000;

The two queries give different results — the second skips entire subtrees rooted at a low-salary manager. Use the first form for "filter the displayed list", the second form for "don't walk past these nodes".

CONNECT BY vs Recursive CTE

The same tree walk can be written with a recursive WITH clause:

WITH org (employee_id, first_name, manager_id, lvl) AS (
  SELECT employee_id, first_name, manager_id, 1
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.first_name, e.manager_id, o.lvl + 1
  FROM   employees e
  JOIN   org o ON e.manager_id = o.employee_id
)
SELECT * FROM org;

On Oracle: prefer CONNECT BY for tree walks. Cross-database code: use recursive CTEs.

Real-world Examples

Generate a Number Series

A surprisingly common use of CONNECT BY is to fabricate a series of numbers — useful for densifying a sparse dataset or building a calendar:

SELECT LEVEL AS n
FROM   dual
CONNECT BY LEVEL <= 10;

Result:

No START WITH needed here — dual has exactly one row, so the walk runs LEVEL <= 10 times.

Generate a Date Series

SELECT DATE '2024-01-01' + (LEVEL - 1) AS day
FROM   dual
CONNECT BY LEVEL <= 7;

Result: seven consecutive days starting 2024-01-01.

Find All Subordinates Beneath a Manager

SELECT employee_id, first_name, last_name, LEVEL AS depth
FROM   employees
START WITH manager_id = 100
CONNECT BY PRIOR employee_id = manager_id;

Everyone reporting to employee 100, transitively.

Find the Reporting Chain Up From an Employee

SELECT first_name, last_name, LEVEL
FROM   employees
START WITH employee_id = 200
CONNECT BY employee_id = PRIOR manager_id;

Employee 200's manager, their manager's manager, and so on up to the root.

Count Indirect Reports

SELECT manager_id, COUNT(*) - 1 AS subordinates
FROM (
  SELECT employee_id, manager_id, CONNECT_BY_ROOT manager_id AS root_mgr
  FROM   employees
  CONNECT BY PRIOR employee_id = manager_id
)
GROUP BY manager_id;

For each manager, the number of people in the org tree underneath them (subtract 1 to exclude themselves).

Common Errors

Interview Corner

START WITH      <which rows are at the top?>
CONNECT BY PRIOR <parent_col> = <child_col>
                 ^── parent's value          ^── child's value

SELECT employee_id,
       first_name,
       LTRIM(SYS_CONNECT_BY_PATH(first_name, ' → '), ' → ') AS chain
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Related Topics

• CTEs & Procedures — recursive CTEs are the cross-database alternative to CONNECT BY
• Subqueries — wrap a CONNECT BY result in an inline view for further processing
• Window Functions — compute totals per branch using OVER (PARTITION BY CONNECT_BY_ROOT ...)
• Aggregate Functions — count subordinates per manager using CONNECT BY + GROUP BY
• Performance — large CONNECT BY queries benefit from indexes on both parent and child columns