Demos

Recursive CTE

The query

Picture

Base tablenever changes

Working table

Result

Ready

Speed 1×

Steps

Pseudocode

Rounds

Every row is tagged with the round that produced it. The round tag is added by this demo to help you read the result: a real result table has no such column.

Reading the screen

Working row being processed
Base row the join matched
Row just added to the result
Row discarded as a repeat

How a recursive CTE builds its result

The ideas

A CTE (common table expression) is a named, temporary result you define with WITH and then query. Adding RECURSIVE lets the definition refer to itself.
A recursive CTE has two parts joined by UNION or UNION ALL: the anchor (a normal query that never mentions the CTE) and the recursive part (a query that reads the CTE).
The database runs it in rounds. Round 0 runs the anchor once. Each later round runs the recursive part on the working table: only the rows the previous round produced, not the whole result so far.
The rows from each round are appended to the result and become the next working table. When a round produces no rows, the working table is empty and the recursion stops.
Finally the outer query (SELECT * FROM team) reads the finished result.

Why it matters

It walks hierarchies and graphs of unknown depth: org charts, category trees, bills of materials, routes. Without it you would need one self join per level.
Cycles. If the data loops (Jaffna to Colombo and back), UNION ALL keeps adding the same cities forever. UNION throws away rows that are already in the result, so the loop dies out. A depth limit such as WHERE level < 5 is the other common guard.

Simplifications

The demo stops after 6 rounds so a runaway query cannot hang the page. A real database keeps going until it runs out of memory or hits a configured limit.
With UNION, rows are compared on every column (so adding a level column can stop it removing repeats). Row order is not guaranteed by SQL; the demo lists rows in the order they are found.
Some databases (SQL Server, Oracle) omit the word RECURSIVE; PostgreSQL, MySQL 8 and SQLite require it.

Try this

Start with Count to 5 and press play: one anchor row, one new row per round, and a WHERE that finally returns nothing.
Open Org chart and watch the tree fill in level by level: the result is built in breadth-first order.
Open Flight routes with UNION ALL, play it, and watch the loop never end. Then switch to UNION and replay: repeats are discarded and the query finishes.
Use ← to step back and see exactly which working row produced each result row.

Keyboard

Space	Play / pause
→ ←	Next / previous step
Home End	Back to start / skip to end
1 – 3	Count / org chart / flight routes
U	Switch UNION ALL / UNION
?	This help

Created by