SQL Recursive Queries: Hierarchies, Depth and Termination

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

A recursive CTE has an anchor term and a recursive term. The anchor selects starting rows, such as Ana at depth zero. The recursive term joins customers to the preceding team rows using child.manager_id=team.id and increments depth. UNION ALL combines each new level with the accumulated result without removing repeated selected rows automatically. The direction of the relationship determines whether the query walks towards managers or towards descendants.

The recursion ends naturally when an iteration finds no more matches. Real data may contain cycles, such as two people naming each other as manager, so a natural stopping assumption can fail. A depth bound limits expansion for safety but can truncate a legitimate deep tree. Robust hierarchy traversal can carry a visited path or use engine features for cycle detection. UNION rather than UNION ALL can remove some repeated rows, but a changing depth or path column makes those rows distinct and does not automatically solve cycles.

Define the recursive columns explicitly when it helps type and meaning remain clear. Some engines infer types from the anchor, so a growing path string may need a sufficiently wide cast there. The anchor and recursive terms must have compatible column counts and types. Aggregate, window and join restrictions inside a recursive term vary by dialect. Begin with a simple identity-and-depth traversal before adding elaborate calculations.

Final ORDER BY defines display order. Recursion’s evaluation process should not be treated as a universal depth-first or breadth-first output promise. Ordering by depth and id gives a simple level-by-level display for this sample; it does not produce a fully indented tree in all applications. If the model is a graph with multiple paths to the same node, decide whether the result should retain each path or list each node once. That decision affects deduplication, path tracking and interpretation of totals.

PostgreSQL, MySQL 8+ and SQLite use WITH RECURSIVE. SQL Server uses WITH without the RECURSIVE keyword and supports an OPTION (MAXRECURSION n) query hint. Engine defaults and recursive-term restrictions differ. PostgreSQL offers SEARCH and CYCLE features, while equivalent path or cycle handling in other engines uses different mechanisms.

Check the engine-specific syntax and version requirements in PostgreSQL recursive WITH, SQLite recursive CTEs.

Traverse Ben’s subtree starting at customer 2. Use the same depth bound and order by depth and id.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

WITH RECURSIVE team (id,name,manager_id,depth) AS (
 SELECT id,name,manager_id,0 FROM customers WHERE id=2
 UNION ALL
 SELECT c.id,c.name,c.manager_id,t.depth+1
 FROM customers AS c JOIN team AS t ON c.manager_id=t.id
 WHERE t.depth<10
)
SELECT id,name,depth FROM team ORDER BY depth,id;

id | name | depth
2 | Ben | 0
4 | Dev | 1

Changing the anchor changes the root-relative depths. Ben is now level zero and Dev is level one. Ana and Cara are not descendants of Ben, so they are absent. In SQL Server adapt the WITH spelling and its documented recursion controls before running this solution.

Start from Cara and predict one row because she has no direct reports. Start from Ben and predict Ben followed by Dev at root-relative depths zero and one. On a separate disposable hierarchy introduce a cycle only after implementing a depth guard or cycle policy, then observe whether the query signals truncation or repeated visits. Avoid unbounded experiments on cyclic input.