How to Query Hierarchies with CONNECT BY in Oracle

Many tables store a tree: each employee points to a manager, each category to a parent category, each part to the assembly it belongs to. Oracle's hierarchical query walks such a tree with two clauses, START WITH and CONNECT BY, and adds pseudocolumns and functions made for trees: the level of each row, the path from the root, the root itself, and whether a row is a leaf.

This guide covers walking down and up a tree, sorting siblings, showing paths, roots, and leaves, handling loops, and generating rows with CONNECT BY LEVEL.

Code for This Guide

The main examples are in the examples/hierarchical folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, whose EMPLOYEES table forms a tree of five levels below the chief executive. Install it with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

select ... from table
[where condition]
start with condition
connect by [nocycle] prior parent_key = child_key [and condition]
[order siblings by expr]

START WITH chooses the root rows. CONNECT BY says how a parent relates to its children, and PRIOR marks the parent's side of the condition.

Walk Down the Tree

START WITH manager_id IS NULL starts at the chief executive. CONNECT BY PRIOR employee_id = manager_id makes every employee whose manager is the prior row a child of it. The pseudocolumn LEVEL is 1 for the root, 2 for its children, and so on; here it indents the names and, in CONNECT BY, stops the walk at the third level.

Example:

select level, lpad(' ', 2 * (level - 1)) || first_name || ' ' || last_name as employee,
       job_title
from   employees
start  with manager_id is null
connect by prior employee_id = manager_id
and    level <= 3
order  siblings by last_name;

Output:

   LEVEL EMPLOYEE                 JOB_TITLE
________ ________________________ ________________________________
       1 Layla Haddad             Chief Executive Officer
       2   Omar Al Mansoori       Chief Operating Officer
       3     James Clarke         Director of Flight Operations
       3     Mateo Fernández      Head of Engineering
       3     Fatima Rahman        Director of Cabin Services
       3     Amelia Smith         Station Manager, London
       2   Charlotte Evans        Chief Commercial Officer
       3     Lin Chen             Customer Care Manager
       3     Arjun Kapoor         Director of Sales
       3     Isabella Martínez    Marketing Manager
       3     Kim Nguyen           Head of Digital
       2   Rohan Mehta            Chief Financial Officer
       3     Ravi Reddy           Finance Manager
       2   Grace Okafor           Chief People Officer
       3     Sofia Costa          HR Business Partner

15 rows selected.

ORDER SIBLINGS BY sorts the children of each parent while keeping every child under its parent. A plain ORDER BY would sort all rows together and destroy the tree's shape.

Walk Up the Tree

Put PRIOR on the other side, and the query walks up instead of down: each row's parent becomes the next row. This climbs from a flight attendant to the chief executive.

Example:

-- walk up from a flight attendant to the chief executive
select level, last_name, job_title
from   employees
start  with employee_id = 140
connect by employee_id = prior manager_id;

Output:

   LEVEL LAST_NAME      JOB_TITLE
________ ______________ _____________________________
       1 O'Brien        Flight Attendant
       2 Okafor         Cabin Manager
       3 Rahman         Director of Cabin Services
       4 Al Mansoori    Chief Operating Officer
       5 Haddad         Chief Executive Officer

A simple way to remember it: PRIOR goes on the column of the row you have just visited. Walking down, you visited the manager, so PRIOR employee_id = manager_id. Walking up, you visited the employee, so employee_id = PRIOR manager_id.

Show the Path with SYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH(column, separator) returns the values of the column on every level from the root to the current row, each preceded by the separator.

Example:

select last_name, sys_connect_by_path(last_name, ' / ') as path
from   employees
where  employee_id in (116, 133, 196)
start  with manager_id is null
connect by prior employee_id = manager_id;

Output:

LAST_NAME    PATH
____________ ____________________________________________________________
Müller        / Haddad / Al Mansoori / Clarke / Sato / Dubois / Müller
Laurent       / Haddad / Al Mansoori / Rahman / Taylor / Laurent
Iyer          / Haddad / Evans / Chen / Iyer

The query walks the whole tree from the root, and the WHERE clause then keeps three employees, each with the full chain of managers above them.

Find Roots and Leaves

The operator CONNECT_BY_ROOT returns a column's value in the root row of the current row's tree, which is useful when a query starts several trees at once. The pseudocolumn CONNECT_BY_ISLEAF is 1 for a row with no children and 0 otherwise.

Example:

select last_name, level, connect_by_root last_name as top_manager,
       connect_by_isleaf as is_leaf
from   employees
start  with employee_id in (130, 180)
connect by prior employee_id = manager_id
order  siblings by last_name;

Output:

LAST_NAME          LEVEL TOP_MANAGER       IS_LEAF
_______________ ________ ______________ __________
Fernández              1 Fernández               0
Li                     2 Fernández               1
Suzuki                 2 Fernández               1
Rahman                 1 Rahman                  0
Okafor                 2 Rahman                  0
Aziz                   3 Rahman                  1
Bianchi                3 Rahman                  1
O'Brien                3 Rahman                  1
Zhang                  3 Rahman                  1
Taylor                 2 Rahman                  0
Brown                  3 Rahman                  1
Laurent                3 Rahman                  1
Sharma                 3 Rahman                  1
van der Berg           3 Rahman                  1

14 rows selected.

WHERE Filters After the Tree Is Built

Oracle first builds the tree from START WITH and CONNECT BY, and only then applies WHERE. So a condition in WHERE removes single rows but keeps their descendants, while the same condition in CONNECT BY stops the walk at that row and cuts off its whole branch.

Example:

-- WHERE removes Rahman's row but keeps the people below Rahman
select level, lpad(' ', 2 * (level - 1)) || last_name as employee
from   employees
where  last_name <> 'Rahman'
start  with last_name = 'Al Mansoori'
connect by prior employee_id = manager_id and level <= 3;

-- the same condition in CONNECT BY cuts off Rahman's whole branch
select level, lpad(' ', 2 * (level - 1)) || last_name as employee
from   employees
start  with last_name = 'Al Mansoori'
connect by prior employee_id = manager_id and level <= 3
       and last_name <> 'Rahman';

Output:

   LEVEL EMPLOYEE
________ ______________
       1 Al Mansoori
       2   Clarke
       3     Sato
       3     Taylor
       3     Okafor
       2   Fernández
       3     Li
       3     Suzuki
       2   Smith
       3     Wilson

10 rows selected.

   LEVEL EMPLOYEE
________ ______________
       1 Al Mansoori
       2   Clarke
       3     Sato
       2   Fernández
       3     Li
       3     Suzuki
       2   Smith
       3     Wilson

8 rows selected.

In the first result, Taylor and Okafor, who report to Rahman, are still there, now appearing to sit under Clarke. In the second, they are gone with Rahman. Decide which you want before choosing the clause.

Handle Loops with NOCYCLE

If the data contains a loop, a row that is its own ancestor, the query fails. Routes form such loops, since you can fly from Auckland to Sydney and back:

Example:

select level, origin, destination
from   routes
start  with origin = 'AKL' and destination <> 'DXB'
connect by prior destination = origin and destination <> 'DXB';

Output:

Error starting at line : 1
In command -
select level, origin, destination
from   routes
start  with origin = 'AKL' and destination <> 'DXB'
connect by prior destination = origin and destination <> 'DXB'
Error at Command Line : 2 Column : 8
Error report -
SQL Error: ORA-01436: CONNECT BY loop in user data

CONNECT BY NOCYCLE returns the rows anyway without following the loop, and the pseudocolumn CONNECT_BY_ISCYCLE is 1 for a row that has a child that is also its ancestor. This walks the network from Auckland without passing through Dubai.

Example:

-- every trip from Auckland that does not pass through Dubai; routes form cycles
-- (AKL>SYD>AKL), so NOCYCLE is needed, and CONNECT_BY_ISCYCLE marks where a cycle closes
select level, 'AKL' || sys_connect_by_path(destination, '>') as path,
       connect_by_iscycle as cycle
from   routes
start  with origin = 'AKL' and destination <> 'DXB'
connect by nocycle prior destination = origin and destination <> 'DXB';

Output:

   LEVEL PATH                  CYCLE
________ __________________ ________
       1 AKL>SYD                   0
       2 AKL>SYD>AKL               1
       2 AKL>SYD>SIN               1
       3 AKL>SYD>SIN>NRT           1

Generate Rows with CONNECT BY LEVEL

A CONNECT BY without PRIOR on the one-row DUAL table simply repeats until its condition fails. It is the most common Oracle idiom for generating a series of numbers or dates.

Example:

select level as n, date '2026-03-01' + level - 1 as day
from   dual
connect by level <= 5;

Output:

   N DAY
____ ______________
   1 01-MAR-2026
   2 02-MAR-2026
   3 03-MAR-2026
   4 04-MAR-2026
   5 05-MAR-2026

Related Guides

• How to Write Recursive Queries with Recursive WITH in Oracle, the standard SQL alternative
• How to Create a Hierarchical Tree in Oracle Forms Using FTREE

Conclusion

START WITH picks the roots and CONNECT BY PRIOR parent = child finds the children; move PRIOR to the other side to walk up. Use LEVEL for depth, ORDER SIBLINGS BY to sort without breaking the tree, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT, and CONNECT_BY_ISLEAF for paths, roots, and leaves, NOCYCLE for data with loops, and CONNECT BY LEVEL to generate rows. Put a condition in CONNECT BY to prune a branch, or in WHERE to drop single rows.