In a recursive CTE over (id, parent_id), what changes when you walk ancestors instead of descendants?

## Two directions over one adjacency list A self-referencing table — `categories(id, name, parent_id)`, `employees(id, name, manager_id)` — stores a hierarchy as one directed edge per row: *this row points at its parent*. The table is symmetric in the sense that the same edge answers two different questions: - **Descendants**: given a node, which rows sit beneath it (its subtree)? - **Ancestors**: given a node, which rows sit above it (the chain up to the root)? A recursive CTE can walk either direction over exactly the same table. Nothing about the schema changes; one join predicate does. ## Descendants: following the edge backwards, fanning out ```sql WITH RECURSIVE subtree (id, name, parent_id) AS ( SELECT c.id, c.name, c.parent_id FROM categories c WHERE c.id = 7 -- start node UNION ALL SELECT child.id, child.name, child.parent_id FROM categories child JOIN subtree s ON child.parent_id = s.id ) SELECT * FROM subtree; ``` Read the recursive member as a question: *which table rows name a row I already have as their parent?* That is `child.parent_id = s.id`. Because many rows can name the same parent, each iteration can produce several rows for every row of the previous iteration — the walk fans out, and the total result is the whole subtree. Its size is bounded by the tree, not by its height. ## Ancestors: following the edge forwards, a single chain ```sql WITH RECURSIVE ancestors (id, name, parent_id) AS ( SELECT c.id, c.name, c.parent_id FROM categories c WHERE c.id = 7 -- start node UNION ALL SELECT p.id, p.name, p.parent_id FROM categories p JOIN ancestors a ON p.id = a.parent_id ) SELECT * FROM ancestors; ``` Now read it as: *which table row is the parent of a row I already have?* That is `p.id = a.parent_id`. Since `parent_id` holds at most one value and `id` is unique, each iteration yields at most one row per row it consumed. The walk is a chain, and it stops on its own: when it reaches the root, `parent_id` is NULL, the join finds nothing, and the recursion hits its fixpoint. The number of iterations is the height of the tree, which is typically small. ## What stays the same The anchor member is identical in both queries — you always start by selecting the node of interest. The `UNION ALL` structure is identical. Any extra columns you carry (a depth counter, an accumulated path) work the same way; only their meaning changes, since depth going up counts steps toward the root rather than away from it. ## Including or excluding the starting row Both queries return the anchor row. That is usually what you want for a subtree ("this category and everything under it") but often not for ancestors ("who are this employee's managers" rarely means "including himself"). Exclude it in the outer query — `WHERE id <> 7` — or carry a depth column and filter `depth > 0`. Deciding this after the fact in the outer `SELECT` is fine; do not try to skip it by starting the anchor one level up, which breaks when the node is already the root. ## Ordering the results Ancestor queries usually want root-first output for a breadcrumb, but the walk generates them child-first. Carry a depth column and `ORDER BY depth DESC`, or accumulate a path. As always, the rows have no guaranteed order without an explicit `ORDER BY`. ## When it looks like both directions at once "Everyone in the same department subtree as this employee" is two walks, not a clever one: go up to find the subtree root, then go down from it. You can write it as two stacked CTEs, the second anchored on the result of the first. Trying to express it as one recursion that follows edges in both directions turns the tree into an undirected graph, which means every node reaches every other node and the query almost certainly needs cycle protection. ## Common mistakes Joining the base table to itself instead of to the CTE — the recursive member must reference the CTE name or it is not recursion at all. Assuming an upward walk can fan out, which only happens if a row has multiple parents (a graph, not a tree). Forgetting that the anchor row is in the output. And reversing `ORDER BY` in the hope that it changes which rows the query finds; ordering is presentation, the join predicate is what chooses the direction.