Recursive Patterns in Practice

Category Subtree

Walk Descendants

A recursive CTE can start at one category and walk all child categories below it.

Program

Play the script to choose a root category and list its descendants with depth.

root_id
category_subtree.sql
Replay: real traced execution (multi-file project)
CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);
INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');
WITH RECURSIVE params(root_id) AS (VALUES (1)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;

CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);
INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');
WITH RECURSIVE params(root_id) AS (VALUES (2)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;

CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);
INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');
WITH RECURSIVE params(root_id) AS (VALUES (3)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;

  1. tables ← 1 row

    1CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');
    values this step1 rowtables

  2. categories ← 6 rows

    1CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');3WITH RECURSIVE params(root_id) AS (VALUES (1)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;
    values this step6 rowscategories

  3. result ← 6 rows

    2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');3WITH RECURSIVE params(root_id) AS (VALUES (1)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;
    values this step6 rowsresult

  1. tables ← 1 row

    1CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');
    values this step1 rowtables

  2. categories ← 6 rows

    1CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');3WITH RECURSIVE params(root_id) AS (VALUES (2)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;
    values this step6 rowscategories

  3. result ← 3 rows

    2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');3WITH RECURSIVE params(root_id) AS (VALUES (2)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');
    values this step1 rowtables

  2. categories ← 6 rows

    1CREATE TABLE categories (id INTEGER, parent_id INTEGER, name TEXT);2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');3WITH RECURSIVE params(root_id) AS (VALUES (3)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;
    values this step6 rowscategories

  3. result ← 2 rows

    2INSERT INTO categories VALUES (1, NULL, 'Store'), (2, 1, 'Books'), (3, 1, 'Tools'), (4, 2, 'SQL'), (5, 2, 'Rust'), (6, 3, 'Garden');3WITH RECURSIVE params(root_id) AS (VALUES (3)), tree(id, name, depth) AS (SELECT id, name, 0 FROM categories WHERE id = (SELECT root_id FROM params) UNION ALL SELECT c.id, c.name, tree.depth + 1 FROM categories AS c JOIN tree ON c.parent_id = tree.id) SELECT id, name, depth FROM tree ORDER BY depth, id;
    values this step2 rowsresult
anchor The anchor query selects the chosen root category.
recursive member The recursive member joins each current category to its children.
depth `depth + 1` records how far each row is from the root.