Recursive Queries · LearningSQL.org

Most SQL queries work on flat data — rows in a table, joined to rows in another table. But some real-world data is inherently hierarchical. An employee reports to a manager who reports to a director. A product is made of components that themselves contain sub-parts. A category has subcategories nested inside it.

To navigate this kind of data in SQL, you need recursive queries. A recursive query is a query that references itself, repeating until a stopping condition is met. They are written with a Common Table Expression that names itself in its own body, conventionally marked WITH RECURSIVE.

What You'll Learn

By the end of this lesson you will write a single query that walks a parent-child table from any starting row all the way to its leaves, and you will be able to say in advance how many rows it returns and what stops it. That second half is the real skill: a recursive query with the wrong join direction returns one row, a recursive query with no stopping condition returns none at all because the environment kills it after fifteen seconds, and neither of those failures produces an error message explaining itself.

This lesson is optional. The core path ends with the capstone you have already built; these last four lessons are extensions to reach for when a job calls for them, in any order. Nothing in the capstone needs recursion. You arrive here able to join a table to another table and read the combined rows (Joins and Relationships), and able to name a query with a CTE and select from it (Views and CTEs); what is new is a CTE that joins a table to the rows that CTE has already produced.

How Recursive CTEs Work

A recursive CTE has two parts combined by a compound operator, usually UNION ALL:

The base case — a regular query that returns the starting rows
The recursive case — a query that references the CTE itself, building on the previous result

The database evaluates the base case first, then runs the recursive case repeatedly, feeding each result back into itself, until the recursive case produces no new rows.

The simplest demonstration is generating a sequence of numbers:

WITH RECURSIVE numbers AS (
  SELECT 1 AS n

  UNION ALL

  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

This works as follows: the base case produces the single row 1. The recursive case takes that row, adds 1, and produces 2. Then 2 becomes 3, and so on, until n < 10 is no longer true and the recursion stops naturally.

Two things about that syntax are conventions rather than requirements on this engine, and it is worth knowing which is which. RECURSIVE is optional here: SQLite accepts a plain WITH for a self-referencing CTE and runs it identically. UNION also works in place of UNION ALL, and it changes the meaning rather than just the spelling — UNION discards duplicate rows between the two parts, which on a clean tree makes no difference and on data with repeated values silently drops rows. Write WITH RECURSIVE and UNION ALL anyway: the keyword tells the next reader what the query is, and UNION ALL says you want every row the recursion produced rather than a de-duplicated set.

What actually stops it

The stopping condition is the single most important thing to be able to point at in a recursive query, because there is no other guard. Here it is WHERE n < 10 inside the recursive case: once the last produced row has n = 10, that filter matches nothing, the recursive case returns no new rows, and the recursion ends.

Take the WHERE away and nothing stops. The recursion keeps producing rows until this environment's fifteen-second limit kills the whole script — you get no output at all and the message command timed out after 15s, which is the runtime giving up rather than the database telling you your query was wrong. The same thing happens with a WHERE in place if the data contains a cycle: if row A's parent is B and B's parent is A, every step finds a new row to follow and the filter never runs out of work.

Predict

The recursive case below has no WHERE clause at all, so nothing ever makes it return zero rows. But the outer query asks for only the first five. What happens when you run it in this environment?

WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers
)
SELECT n FROM numbers LIMIT 5;

It times out after fifteen seconds with no output, because the CTE has to finish building before the outer query can take five rows from it.
It prints 1 through 5 and exits cleanly, because the LIMIT stops the outer query asking for more rows and the recursion is only driven as far as it is consumed.
It raises an error about a recursive query with no terminating condition, because the engine checks for one before running.

Traversing an Organizational Hierarchy

The classic use case for recursive queries is navigating a hierarchy stored in a self-referencing table — a table where one column is a foreign key pointing back to the same table's primary key.

CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  title      TEXT NOT NULL,
  manager_id INTEGER
);

INSERT INTO employees VALUES (1, 'Alice',  'CEO',                 NULL);
INSERT INTO employees VALUES (2, 'Bob',    'VP of Engineering',   1);
INSERT INTO employees VALUES (3, 'Carol',  'VP of Sales',         1);
INSERT INTO employees VALUES (4, 'Dave',   'Engineering Manager', 2);
INSERT INTO employees VALUES (5, 'Eve',    'Sales Manager',       3);
INSERT INTO employees VALUES (6, 'Frank',  'Senior Engineer',     4);
INSERT INTO employees VALUES (7, 'Grace',  'Engineer',            4);
INSERT INTO employees VALUES (8, 'Heidi',  'Account Executive',   5);

WITH RECURSIVE org_chart AS (
  -- Base case: start with Bob
  SELECT id, name, title, manager_id, 0 AS depth
  FROM employees
  WHERE id = 2

  UNION ALL

  -- Recursive case: find everyone who reports to someone already in the result
  SELECT e.id, e.name, e.title, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT depth, name, title FROM org_chart ORDER BY depth, name;

The depth column is a running count of how many levels deep each employee sits from the starting point. It is a useful pattern for indenting output, limiting traversal, or detecting problems in malformed data.

The join direction is the whole query

Notice which way the join points: e.manager_id = oc.id. Read that as a sentence and the direction stops being arbitrary — bring me every employee whose manager is someone I have already found. The CTE name is the set found so far; the table alias is the candidate row being considered, and which of them carries the id in the ON clause is what decides whether the traversal moves down the tree or up it.

That makes the ON clause the first thing to check when a recursion comes back shorter than the data. The block below is one that does.

Debug

This query is meant to list a category tree from its root downward: Electronics at depth 0, its two children at depth 1, and their children at depth 2. It runs, exits cleanly, and prints exactly one row — Electronics — as if the tree had no other rows in it, even though five categories exist. Commit a hypothesis about what the recursive case is actually asking for on each pass, then fix it so all five categories come back.

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL, parent_id INTEGER);
INSERT INTO categories VALUES (1,'Electronics',NULL),(2,'Computers',1),(3,'Laptops',2),(4,'Phones',1),(5,'Smartphones',4);

WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c
JOIN tree t ON c.id = t.parent_id
)
SELECT depth, name FROM tree ORDER BY depth, name;

Expected output:

5 row(s) affected
depth | name
------------
0 | Electronics
1 | Computers
1 | Phones
2 | Laptops
2 | Smartphones

Before you change anything — why do you think this fails?

Building a Path String

Another common pattern is accumulating a path as you traverse. Instead of just tracking depth, you build a string that shows the full ancestry of each node — useful for breadcrumb navigation or audit trails.

CREATE TABLE categories (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  parent_id INTEGER
);

INSERT INTO categories VALUES (1, 'Electronics',  NULL);
INSERT INTO categories VALUES (2, 'Computers',    1);
INSERT INTO categories VALUES (3, 'Laptops',      2);
INSERT INTO categories VALUES (4, 'Gaming',       2);
INSERT INTO categories VALUES (5, 'Phones',       1);
INSERT INTO categories VALUES (6, 'Smartphones',  5);
INSERT INTO categories VALUES (7, 'Accessories',  5);

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, name AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT path FROM category_tree ORDER BY path;

The || operator concatenates strings. Each recursive step appends the current category name to the path built by its parent, producing a full breadcrumb trail for every node in the tree.

Reading the Result of a Traversal

Once the recursion has produced its rows, the outer query is an ordinary query over them — and the ordinary clause order applies unchanged. The CTE below walks a reporting chain; the five lines under it summarise the result by level, keeping only the levels with more than one person on them.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER);
INSERT INTO employees VALUES (1,'Alice',NULL),(2,'Bob',1),(3,'Carol',1),(4,'Dave',2),(5,'Eve',3),(6,'Frank',4);
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id
)

Every arrangement below runs against that CTE, which is already given.

Arrange the code

These five lines are the outer query over the recursive CTE above: it counts how many people sit at each level below the root, keeping only the levels holding more than one person. They are shuffled. Put them in the order that runs — then answer what the order is really testing: the recursion has already finished by the time these lines run, so why can HAVING still not trade places with WHERE?

FROM chain
WHERE depth > 0
GROUP BY depth
SELECT depth, COUNT(*) AS people
HAVING COUNT(*) > 1

Try It Yourself

A bill of materials is a manufacturing idea: a finished product is assembled from components, which are themselves made of sub-components. That is a self-referencing table, so it maps onto a recursive query exactly.

The starter below has the table, the data and the base case — but no recursive case at all, so the CTE is not recursive yet and it returns the single starting row. Your job is to add the second half.

Build

The starter walks a bill of materials from the Wheels sub-assembly downward, building a breadcrumb path for every part underneath it. Its CTE has only a base case, so it returns one row: Wheels. Work TODO 1 and TODO 2 so the query returns all five rows of the Wheels branch, each one a full path from Wheels down to that part, sorted alphabetically.

CREATE TABLE components (id INTEGER PRIMARY KEY, name TEXT NOT NULL, parent_id INTEGER);
INSERT INTO components VALUES
(1,'Bicycle',NULL),(2,'Frame',1),(3,'Wheels',1),(4,'Drivetrain',1),
(5,'Front Wheel',3),(6,'Rear Wheel',3),(7,'Chain',4),(8,'Cassette',4),
(9,'Spokes',5),(10,'Hub',5);

-- TODO 1: add a recursive case to the CTE below, combined with UNION ALL, that
--         selects every component whose parent is already in bom.
--         Front Wheel has parent_id 3, which is Wheels, so it joins on the first pass.
-- TODO 2: in that recursive case build the path by appending to the parent's path,
--         separated by a space, a greater-than sign and a space.
--         Front Wheel's path must read: Wheels > Front Wheel
--         Hub sits under Front Wheel, so its path must read: Wheels > Front Wheel > Hub
WITH RECURSIVE bom AS (
SELECT id, name, parent_id, name AS path
FROM components
WHERE name = 'Wheels'
)
SELECT path FROM bom ORDER BY path;

Expected output:

10 row(s) affected
path
----
Wheels
Wheels > Front Wheel
Wheels > Front Wheel > Hub
Wheels > Front Wheel > Spokes
Wheels > Rear Wheel

Before you run — what's your plan for the first TODO?

Once it passes, try two variations and predict the result before you run each. First, change WHERE name = 'Wheels' to WHERE parent_id IS NULL — the traversal now starts at Bicycle and returns all ten rows, each path beginning with Bicycle. Second, put the base case back and change UNION ALL to UNION — the output is identical here, because every path string in this tree is distinct, which is exactly why the difference between the two is so easy to miss until a duplicate row appears and quietly disappears.

When UNION and UNION ALL stop agreeing

Every tree in this lesson has been a true tree: each node has exactly one parent, so no node is ever reached twice and UNION and UNION ALL produce the same rows. A graph is different. Below, leaf has two parents, so there are two distinct paths from the root to it.

Predict

Four edges describe a diamond: the root has two children, a and b, and both of them have the same child, leaf. The query walks from the root and counts how many times each node appears in the result. What does it report for leaf, and what would change if UNION ALL were plain UNION?

CREATE TABLE edges (parent TEXT NOT NULL, child TEXT NOT NULL);
INSERT INTO edges VALUES ('root','a'),('root','b'),('a','leaf'),('b','leaf');

WITH RECURSIVE walk AS (
SELECT 'root' AS node
UNION ALL
SELECT e.child FROM edges e JOIN walk w ON e.parent = w.node
)
SELECT node, COUNT(*) AS times FROM walk GROUP BY node ORDER BY node;

Leaf appears once with either operator, because the recursion visits each node once and remembers where it has been.
Leaf appears twice with UNION ALL, once for each path that reaches it, and would appear once with UNION because the duplicate row is discarded.
The query never terminates with UNION ALL, because leaf is reached twice and the recursion loops.

Recall

No notes. In 09-views-and-ctes you named a query with WITH and selected from it. A recursive CTE differs from the ones you wrote there in one specific way. Which statement describes it?

A recursive CTE may name itself inside its own body, so its second branch reads the rows the CTE has produced so far; an ordinary CTE can only refer to tables and to CTEs defined before it.
A recursive CTE is stored in the database and re-evaluated on each query, whereas an ordinary CTE exists only for the statement that defines it.
A recursive CTE can be used more than once in the outer query, while an ordinary CTE can only be selected from once.

The Same Answer Without Recursion

Transfer

The two queries below both list a category tree down to depth 2, and on this data they return byte-identical output: Electronics at 0, Computers and Phones at 1, Laptops at 2. The first is the recursive CTE you have been writing; the second is a chain of self-joins with no recursion at all. Given that they agree here, what is the real difference between them?

CREATE TABLE cat (id INTEGER PRIMARY KEY, name TEXT NOT NULL, parent_id INTEGER);
INSERT INTO cat VALUES (1,'Electronics',NULL),(2,'Computers',1),(3,'Laptops',2),(4,'Phones',1);

-- Shape A: recursion
WITH RECURSIVE t AS (
SELECT id, name, parent_id, 0 AS depth FROM cat WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth+1 FROM cat c JOIN t ON c.parent_id = t.id
)
SELECT name, depth FROM t WHERE depth <= 2 ORDER BY depth, name;

-- Shape B: one self-join per level
SELECT name, 0 AS depth FROM cat WHERE parent_id IS NULL
UNION ALL
SELECT c.name, 1 FROM cat c JOIN cat p ON c.parent_id = p.id WHERE p.parent_id IS NULL
UNION ALL
SELECT c.name, 2 FROM cat c JOIN cat p ON c.parent_id = p.id JOIN cat g ON p.parent_id = g.id WHERE g.parent_id IS NULL
ORDER BY depth, name;

Shape B is faster because it avoids recursion, so the recursive form is only worth using when the query has to be short.
Shape B hard-codes the depth: it has one join per level, so it can only reach as deep as it was written for, while shape A follows the data to whatever depth it happens to have.
Shape A and shape B are fully interchangeable, since any recursive CTE can be rewritten as a finite chain of self-joins.

Key Takeaways

A recursive CTE consists of a base case and a recursive case combined with UNION ALL, and the recursive case is the one that names the CTE inside its own body
RECURSIVE is optional on this engine and UNION works in place of UNION ALL; write WITH RECURSIVE and UNION ALL anyway, because the keyword documents intent and UNION silently discards duplicate rows
The recursion stops when the recursive case returns no new rows — nothing else guards it, and a missing or unreachable stopping condition ends in this environment's fifteen-second timeout rather than an error
The side of the ON clause that carries the CTE alias decides the direction of travel; a recursion that returns only its base case almost always has that backwards
Path accumulation, a depth counter and a cycle guard are the same pattern: carry a value forward and add to it on every pass
Recursive CTEs are supported in PostgreSQL, SQLite, MySQL 8+ and most modern engines; PostgreSQL's reference is PostgreSQL 16 documentation: WITH Queries, Common Table Expressions and SQLite's is SQLite documentation: The WITH Clause, recursive common table expressions

Pro Tip: If your data could contain cycles — node A has B as a parent, and B has A as a parent — the recursion never runs out of new rows to produce. Measured here: a two-row cycle ends with command timed out after 15s and exit code 124, with no output and no message naming the cycle. Guard it explicitly. WHERE depth < 50 in the recursive case is the cheap version and is enough while developing; the honest version carries the visited ids forward in the accumulated path and refuses a row whose id is already in it, which stops at the cycle rather than at an arbitrary depth.

Next Steps

Now that you can traverse hierarchical data, the next optional lesson explores JSON in SQL — how to store, query, and manipulate semi-structured data directly in your database without leaving the comfort of SQL.