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.

category_subtree.sql 
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 ()), 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 ()), 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 ()), 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;
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.