Some data has no fixed depth: an organization chart with managers over managers, a parts list with assemblies inside assemblies, a route network where one flight connects to the next. A recursive WITH query walks such data to any depth. It is the SQL-standard way to do it, and in Oracle it also generates series of numbers and dates.
This guide explains how a recursive common table expression runs, how to order its result with SEARCH, how to stop cycles with CYCLE, and how to generate rows.
Code for This Guide
The main examples are in the examples/with-clause folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
How Recursion Works
A recursive CTE has two queries joined by UNION ALL:
- The anchor query returns the starting rows.
- The recursive query joins the CTE to itself to produce the next rows from the rows just produced.
Oracle runs the anchor, then the recursive query on the anchor's rows, then again on the rows that produced, and so on until a pass returns no rows.
Syntax:
with name (column [, ...]) as (
anchor_query
union all
recursive_query -- joins to name
)
[search {depth | breadth} first by column [, ...] set ordering_column]
[cycle column [, ...] set cycle_mark to 'value' default 'value']
select ... from name;Walk an Organization Chart
This query starts at the Director of Flight Operations (employee 110) and repeatedly adds the employees who report to someone already found. A level column counts the steps, and the main query stops at level 3 and indents each name by its level.
Example:
-- the reporting chain below the Director of Flight Operations
with chain (employee_id, name, manager_id, lvl) as (
select employee_id, first_name || ' ' || last_name, manager_id, 1
from employees where employee_id = 110
union all
select e.employee_id, e.first_name || ' ' || e.last_name, e.manager_id, c.lvl + 1
from employees e join chain c on e.manager_id = c.employee_id
)
select lpad(' ', 2 * (lvl - 1)) || name as org_chart, lvl
from chain
where lvl <= 3;Output:
ORG_CHART LVL
__________________ ______
James Clarke 1
Kenji Sato 2
Hugo Dubois 3
Ananya Iyer 3
Lucas Silva 3
Mei Wong 3
6 rows selected.A recursive CTE must list its column names after its name. Leaving the list out fails:
Example:
with chain as ( select employee_id, manager_id from employees where employee_id = 110 union all select e.employee_id, e.manager_id from employees e join chain c on e.manager_id = c.employee_id ) select count(*) from chain;
Output:
Error starting at line : 1 In command - with chain as ( select employee_id, manager_id from employees where employee_id = 110 union all select e.employee_id, e.manager_id from employees e join chain c on e.manager_id = c.employee_id ) select count(*) from chain Error at Command Line : 1 Column : 12 Error report - SQL Error: ORA-32039: missing column alias list in recursive WITH clause element CHAIN
Order the Result with SEARCH
The rows of a recursive CTE come out in no guaranteed order. The SEARCH clause numbers them in a column you name, and ordering by that column gives a useful shape:
- SEARCH DEPTH FIRST puts each row's descendants right after it, like an indented outline.
- SEARCH BREADTH FIRST puts all rows of one level before the next level.
This compares both orders from the chief executive down, limiting the recursion to three levels with a condition in the recursive query.
Example:
with chain (employee_id, name, manager_id, lvl) as (
select employee_id, last_name, manager_id, 1
from employees where employee_id = 100
union all
select e.employee_id, e.last_name, e.manager_id, c.lvl + 1
from employees e join chain c on e.manager_id = c.employee_id
where c.lvl < 3
)
search depth first by name set seq
select seq, lpad(' ', 2 * (lvl - 1)) || name as depth_first
from chain
order by seq
fetch first 8 rows only;
with chain (employee_id, name, manager_id, lvl) as (
select employee_id, last_name, manager_id, 1
from employees where employee_id = 100
union all
select e.employee_id, e.last_name, e.manager_id, c.lvl + 1
from employees e join chain c on e.manager_id = c.employee_id
where c.lvl < 3
)
search breadth first by name set seq
select seq, lvl, name as breadth_first
from chain
order by seq
fetch first 8 rows only;Output:
SEQ DEPTH_FIRST
______ ________________
1 Haddad
2 Al Mansoori
3 Clarke
4 Fernández
5 Rahman
6 Smith
7 Evans
8 Chen
8 rows selected.
SEQ LVL BREADTH_FIRST
______ ______ ________________
1 1 Haddad
2 2 Al Mansoori
3 2 Evans
4 2 Mehta
5 2 Okafor
6 3 Chen
7 3 Clarke
8 3 Costa
8 rows selected.Depth first shows Al Mansoori followed by the people under Al Mansoori before moving on to Evans. Breadth first lists all of level 2 before any of level 3. In both, BY name sorts siblings alphabetically.
Stop Cycles with CYCLE
A hierarchy has no loops, but a network does: a route from Dubai to London has a route back. Recursing over a network without protection would go round forever. The CYCLE clause marks a row whose value already appears on its own path and stops recursing from it.
This query finds the ways from Auckland to London with at most two changes, never visiting an airport twice.
Example:
-- routes from Auckland with at most two changes, without visiting an airport twice
with trip (airport, path, stops, km) as (
select cast('AKL' as varchar2(3)), cast('AKL' as varchar2(40)), 0, 0 from dual
union all
select r.destination, t.path || '>' || r.destination, t.stops + 1, t.km + r.distance_km
from trip t join routes r on r.origin = t.airport
where t.stops < 3
)
search depth first by airport set seq
cycle airport set is_cycle to 'Y' default 'N'
select path, km from trip
where airport = 'LHR' and is_cycle = 'N'
order by km;Output:
PATH KM __________________ ________ AKL>DXB>LHR 19697 AKL>SYD>DXB>LHR 19699 AKL>DXB>JFK>LHR 30741
The path column builds a readable trail as the recursion proceeds, km adds up the distances, and is_cycle = 'N' discards the looping rows. The condition t.stops < 3 also bounds the search, which is good practice on any network.
Generate Rows
A recursive CTE that adds one to the previous row generates a series, which you can join data to. This query makes a row for each day of a week and counts the flights departing that day, including days that would otherwise be missing.
Example:
-- a row for every day of a week
with days (d) as (
select date '2026-03-09' from dual
union all
select d + 1 from days where d < date '2026-03-15'
)
select d, to_char(d, 'Dy') as weekday,
(select count(*) from flights f
where trunc(cast(f.scheduled_departure at time zone 'UTC' as date)) = days.d)
as flights
from days;Output:
D WEEKDAY FLIGHTS ______________ __________ __________ 09-MAR-2026 Mon 28 10-MAR-2026 Tue 36 11-MAR-2026 Wed 28 12-MAR-2026 Thu 36 13-MAR-2026 Fri 30 14-MAR-2026 Sat 28 15-MAR-2026 Sun 36 7 rows selected.
Things to Know
- Use UNION ALL between the anchor and recursive queries; UNION is not allowed.
- Give columns in the anchor the type and length the recursion needs. The path above is cast to varchar2(40) in the anchor, because a value of length 3 would be too short for longer paths.
- Oracle's older CONNECT BY syntax does the same for hierarchies, with functions such as SYS_CONNECT_BY_PATH. Recursive WITH is standard SQL and also handles networks and running calculations.
Related Guides
Conclusion
A recursive WITH query joins a common table expression to itself, starting from an anchor query and repeating until no new rows appear. List its columns, order the result with SEARCH DEPTH FIRST or BREADTH FIRST, guard networks with CYCLE and a depth limit, and use the same technique to generate series of numbers and dates.
