How to write a recursive query for tree data in SQL

1WITH RECURSIVE: every descendant of a nodeRecommended

A recursive CTE has two parts joined by UNION ALL. The anchor member runs once and returns the starting row, here Books. The recursive member refers to the CTE itself: it joins categories to the rows found so far and returns their children. The database repeats it on each new batch of rows and stops when a pass returns nothing. Carrying a depth column, which starts at 0 and grows by 1 per pass, tells you how far below the start each row sits.

CREATE TABLE categories (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT);
INSERT INTO categories VALUES
    (1, NULL, 'Shop'),
    (2, 1, 'Books'),   (3, 1, 'Music'),
    (4, 2, 'Fiction'), (5, 2, 'Science'), (8, 3, 'Jazz'),
    (6, 4, 'Fantasy'), (7, 4, 'Crime');

-- Everything under Books (id 2), with how far below it each row sits
WITH RECURSIVE tree (id, name, depth) AS (
    -- Anchor member: the starting row
    SELECT id, name, 0
    FROM categories
    WHERE id = 2
    UNION ALL
    -- Recursive member: the children of the rows found so far
    SELECT c.id, c.name, t.depth + 1
    FROM categories AS c
    JOIN tree AS t ON c.parent_id = t.id
)
SELECT id, name, depth
FROM tree
ORDER BY depth, id;

Books comes back at depth 0, its children Fiction and Science at 1, and Fantasy and Crime at 2. Music, Jazz and Shop are left out because they aren't below Books. To get only the descendants, add WHERE depth > 0 to the outer query; to start from the root, make the anchor WHERE parent_id IS NULL. The column list after tree names the CTE's columns, and the anchor decides how many there are. The rows come out in no guaranteed order, so the outer ORDER BY matters.

2A path string and an indented outline

Anything the recursive member computes from the parent row is carried down to the child. Start a path with the root's name and append ' > ' || c.name on each pass, and every row gets its full trail from the root. Sorting by that path puts each child straight after its parent, which is also the order you want for an indented outline: pad the name with two spaces per level.

CREATE TABLE categories (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT);
INSERT INTO categories VALUES
    (1, NULL, 'Shop'),
    (2, 1, 'Books'),   (3, 1, 'Music'),
    (4, 2, 'Fiction'), (5, 2, 'Science'), (8, 3, 'Jazz'),
    (6, 4, 'Fantasy'), (7, 4, 'Crime');

-- The whole tree as paths, sorted so each child follows its parent
WITH RECURSIVE tree (id, path) AS (
    SELECT id, name FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, t.path || ' > ' || c.name
    FROM categories AS c
    JOIN tree AS t ON c.parent_id = t.id
)
SELECT path FROM tree ORDER BY path;

-- The same walk as an indented outline
WITH RECURSIVE tree (id, name, depth, path) AS (
    SELECT id, name, 0, name FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, t.depth + 1, t.path || ' > ' || c.name
    FROM categories AS c
    JOIN tree AS t ON c.parent_id = t.id
)
SELECT substr('        ', 1, 2 * depth) || name AS outline
FROM tree
ORDER BY path;

The paths run from Shop to Shop > Books > Fiction > Fantasy, and the outline prints the same eight rows indented by level. Sorting by a path of names puts siblings in alphabetical order (Crime before Fantasy); if siblings have their own sort column, build the path from that instead. The path is text that grows by one name per level, which SQLite and PostgreSQL (with TEXT) accept as it is. MySQL and SQL Server fix the column's type from the anchor, so the anchor needs a CAST to a wide enough type: see the table in section 7.

3Walk up to the root: the ancestors of a node

To go up instead of down, flip the join. The anchor is the node you start from, and the recursive member finds the row whose id equals the current row's parent_id. A node has one parent, so each pass adds exactly one row until it reaches the root, whose parent_id is NULL and matches nothing. That is how breadcrumbs are built; aggregating the names with string_agg turns them into one string (more on that in combine rows into one string).

CREATE TABLE categories (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT);
INSERT INTO categories VALUES
    (1, NULL, 'Shop'),
    (2, 1, 'Books'),   (3, 1, 'Music'),
    (4, 2, 'Fiction'), (5, 2, 'Science'), (8, 3, 'Jazz'),
    (6, 4, 'Fantasy'), (7, 4, 'Crime');

-- Walk up from Fantasy (id 6) to the root
WITH RECURSIVE up (id, parent_id, name, level) AS (
    SELECT id, parent_id, name, 0 FROM categories WHERE id = 6
    UNION ALL
    SELECT p.id, p.parent_id, p.name, u.level + 1
    FROM categories AS p
    JOIN up AS u ON p.id = u.parent_id
)
SELECT id, name, level FROM up ORDER BY level;

-- The same rows as one breadcrumb, root first
WITH RECURSIVE up (id, parent_id, name, level) AS (
    SELECT id, parent_id, name, 0 FROM categories WHERE id = 6
    UNION ALL
    SELECT p.id, p.parent_id, p.name, u.level + 1
    FROM categories AS p
    JOIN up AS u ON p.id = u.parent_id
)
SELECT string_agg(name, ' > ' ORDER BY level DESC) AS breadcrumb
FROM up;

The walk returns Fantasy, Fiction, Books and Shop at levels 0 to 3, and the breadcrumb reads Shop > Books > Fiction > Fantasy. The ancestors CTE has to carry parent_id, because the next pass joins on it. string_agg with an ORDER BY inside needs SQLite 3.44 or later; PostgreSQL has the same syntax, and MySQL spells it GROUP_CONCAT(name ORDER BY level DESC SEPARATOR ' > ').

4Count the descendants of every node

To answer "how many items sit under each category?" in one query, let the anchor return every row paired with itself, and let the recursive member carry that first id (root_id) down through the tree. The result is one row per pair of a node and something at or below it. Group by root_id and count, subtracting 1 for the node itself.

CREATE TABLE categories (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT);
INSERT INTO categories VALUES
    (1, NULL, 'Shop'),
    (2, 1, 'Books'),   (3, 1, 'Music'),
    (4, 2, 'Fiction'), (5, 2, 'Science'), (8, 3, 'Jazz'),
    (6, 4, 'Fantasy'), (7, 4, 'Crime');

-- One row per (node, node at or below it), starting with each node and itself
WITH RECURSIVE pairs (root_id, id) AS (
    SELECT id, id FROM categories
    UNION ALL
    SELECT p.root_id, c.id
    FROM categories AS c
    JOIN pairs AS p ON c.parent_id = p.id
)
SELECT c.id, c.name, COUNT(*) - 1 AS descendants
FROM pairs AS p
JOIN categories AS c ON c.id = p.root_id
GROUP BY c.id, c.name
ORDER BY c.id;

Shop has 7 descendants (every other row), Books 4, Fiction 2, Music 1, and the four leaves 0. The pairs CTE is a closure table computed on the fly: if you need these counts often on a large tree, storing that table, or a count column you maintain, is cheaper than recomputing it. Swap COUNT(*) for SUM over a joined amount to total the products or sales under each category the same way.

5Guard against cycles

A tree has no loops, but a table can: one bad update that makes a row the child of its own descendant, and a plain recursive query keeps going around the circle forever. There are two portable guards. A depth limit stops after a fixed number of levels. A path check carries the ids visited so far and refuses to step onto one again, which stops exactly where the loop closes. Here Docs, Work and Old point at each other in a circle.

CREATE TABLE folders (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT);
-- A bad update made Docs the child of its own grandchild: 2 -> 3 -> 4 -> 2
INSERT INTO folders VALUES
    (1, NULL, 'Root'),
    (2, 4, 'Docs'),
    (3, 2, 'Work'),
    (4, 3, 'Old');

-- Guard 1: a depth limit stops the loop, but repeats rows on the way
WITH RECURSIVE tree (id, name, depth) AS (
    SELECT id, name, 0 FROM folders WHERE id = 2
    UNION ALL
    SELECT f.id, f.name, t.depth + 1
    FROM folders AS f
    JOIN tree AS t ON f.parent_id = t.id
    WHERE t.depth < 4
)
SELECT id, name, depth FROM tree;

-- Guard 2: carry the visited ids in a path and skip any id already in it
WITH RECURSIVE tree (id, name, path) AS (
    SELECT id, name, '/' || id || '/' FROM folders WHERE id = 2
    UNION ALL
    SELECT f.id, f.name, t.path || f.id || '/'
    FROM folders AS f
    JOIN tree AS t ON f.parent_id = t.id
    WHERE t.path NOT LIKE '%/' || f.id || '/%'
)
SELECT id, name, path FROM tree;

The depth limit ends the query, but Docs and Work come back a second time at depths 3 and 4. The path check returns each folder once, with paths /2/, /2/3/ and /2/3/4/, and stops when Old points back at 2. Without either guard, this page's run ends with Execution timed out, because SQLite has no recursion limit of its own. If the CTE has no column that changes per pass (no depth, no path), writing UNION instead of UNION ALL also ends a cycle, since it discards rows already produced; see UNION vs UNION ALL. Run on this table, that version returns Docs, Work and Old once each.

6The simplest recursive CTE: a number series

Recursion doesn't need a table at all. An anchor that returns 1 and a recursive member that adds 1 while x < 5 produce a series of numbers, which is the smallest example of the anchor, UNION ALL, recursive-member pattern, and a handy way to make rows to join against (one per day of a month, one per slot in a schedule). The WHERE in the recursive member is what ends it.

-- The numbers 1 to 5: an anchor row, then "add 1" until the WHERE stops it
WITH RECURSIVE n (x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 5
)
SELECT x, x * x AS squared FROM n;

It prints 1 to 5 with their squares. Each pass sees only the row the previous pass made, so x + 1 counts up by one. Drop the WHERE and the series never ends, the same failure as a cycle in a tree. A series longer than 100 rows on SQL Server, or 1000 on MySQL, runs into their recursion limits, which the table below shows how to raise.

7PostgreSQL, MySQL and SQL Server

The anchor, UNION ALL, recursive-member shape is standard SQL and works everywhere below. The differences are the keyword, how the CTE's column types are fixed, and the built-in recursion limits. Oracle runs recursive WITH without the RECURSIVE keyword and also has its older CONNECT BY PRIOR syntax.