Recursive Queries

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 using WITH RECURSIVE, an extension of the Common Table Expression (CTE) syntax.

How Recursive CTEs Work

A recursive CTE has two parts joined by UNION ALL:

1. The base case — a regular query that returns the starting rows
2. 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.

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.

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.

Try It Yourself

A bill of materials (BOM) is a manufacturing concept where a finished product is assembled from components, which are themselves made of sub-components. This maps naturally to a recursive query.

The table below stores components and what they belong to. The query is already written — run it, then experiment by changing the starting name from 'Bicycle' to 'Wheels' or 'Drivetrain' to see only that branch of the hierarchy.

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

INSERT INTO components VALUES (1,  'Bicycle',     NULL);
INSERT INTO components VALUES (2,  'Frame',       1);
INSERT INTO components VALUES (3,  'Wheels',      1);
INSERT INTO components VALUES (4,  'Drivetrain',  1);
INSERT INTO components VALUES (5,  'Front Wheel', 3);
INSERT INTO components VALUES (6,  'Rear Wheel',  3);
INSERT INTO components VALUES (7,  'Chain',       4);
INSERT INTO components VALUES (8,  'Cassette',    4);
INSERT INTO components VALUES (9,  'Spokes',      5);
INSERT INTO components VALUES (10, 'Hub',         5);

WITH RECURSIVE bom AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM components
  WHERE name = 'Bicycle'

  UNION ALL

  SELECT c.id, c.name, c.parent_id, bom.depth + 1
  FROM components c
  JOIN bom ON c.parent_id = bom.id
)
SELECT depth, name FROM bom ORDER BY depth, name;

Try adding a new component row that belongs to Rear Wheel (id 6) and re-run — your new part should appear automatically in the output.

Key Takeaways

• Recursive CTEs use WITH RECURSIVE and consist of a base case and a recursive case joined by UNION ALL
• The recursion stops automatically when the recursive case returns no new rows
• A depth counter tracks traversal level and helps guard against runaway queries on bad data
• Path accumulation using string concatenation produces full ancestry strings from any node in a tree
• Self-referencing tables — where parent_id points back to id in the same table — are the primary target for recursive queries
• Recursive CTEs are supported in PostgreSQL, SQLite, MySQL 8+, SQL Server, and most modern databases

Pro Tip: If your data could contain cycles — where node A has B as a parent, and B has A as a parent — the recursion will run forever. Add a WHERE depth < 50 guard clause to the recursive case while developing, or build a cycle check by verifying that the current row's ID does not already appear as a substring in the accumulated path string.

Next Steps

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