Recursive CTEs: The Ultimate SQL Brain Teaser

Welcome to **Day 53**. Today we tackle the "Final Boss" of CTEs: Recursion.

What is a Recursive CTE?

A recursive CTE is a query that **refers to itself**. It's used to navigate hierarchical data—things that have a "Parent-Child" relationship.

Imagine an `employees` table where every row has a `manager_id`. How do you find everyone who reports (directly or indirectly) to the CEO?

WITH RECURSIVE org_chart AS (

-- 1. Base Case: Find the CEO

SELECT id, name, manager_id, 1 AS level

FROM employees

WHERE manager_id IS NULL

UNION ALL

-- 2. Recursive Step: Find people who report to anyone already in the list

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;

1. It runs the "Base Case" first.

2. It takes those results and joins them back to the original table until no more matches are found.

Always ensure your data has a clear "Stop" point. If Employee A reports to B, and B reports to A, your query will run forever and crash the server.

Draw out an Org Chart on paper and trace how the recursive query would find each person.

*Day 54: CTEs vs Temp Tables—Which should you use?*