Recursive CTEs: The Mind Bender

The Full Org Chart

HR needed to see the complete reporting chain for an employee. "Who reports to Sarah? And who reports to them? And who reports to those people?" This is a hierarchy with an unknown depth.

The Quest: Recursion

A `Recursive CTE` is a CTE that references itself. It has two parts:

1. **Anchor Member**: The base case (e.g., Sarah herself).

2. **Recursive Member**: The query that references the CTE and "drills down."

The Implementation: The Syntax

WITH RECURSIVE org_chart AS (

-- Anchor: Start with Sarah

SELECT id, name, manager_id, 1 AS level

FROM employees

WHERE name = 'Sarah'

UNION ALL

-- Recursive: Find everyone who reports to the previous level

SELECT e.id, e.name, e.manager_id, oc.level + 1

FROM employees e

JOIN org_chart oc ON e.manager_id = oc.id

)

SELECT * FROM org_chart;

How It Works

1. The anchor runs and finds Sarah.

2. The recursive part runs, finding everyone whose `manager_id` is Sarah's `id`.

3. It keeps running, finding the next level, until no more rows are found.

The "Oops" Moment

A recursive CTE without a proper exit condition can run forever. I once missed a condition and the query spiraled until the database killed it.

**Pro Tip**: Always test with `LIMIT` during development and ensure your data doesn't have circular references (e.g., A reports to B, B reports to A).

The Victory

HR saw the entire org tree in a single, elegant query. Recursive CTEs are the only practical way to handle hierarchical data in SQL.

Your Task for Today

Generate a simple number series from 1 to 10 using a recursive CTE.

*Day 51-60: Continue Phase 4 with more advanced topics...*