A recursive CTE is a query that references itself, enabling iteration without loops. Think of it as a SQL while loop: start with base data (anchor), then repeatedly apply a rule (recursive member) until no new rows appear. Perfect for trees, graphs, and sequences.

**Recursive CTE Structure (3 Required Parts):**

1. **Anchor Member (Base Case)** - Starting point, executed once

2. **UNION ALL** - Combines anchor and recursive results

3. **Recursive Member (Iteration)** - References CTE itself, must have termination

**Execution Flow:**

Iteration 0: Execute anchor → Result Set R0

Iteration 1: Execute recursive with R0 → Result Set R1

Iteration 2: Execute recursive with R1 → Result Set R2

...

Iteration N: No new rows → STOP

**Termination Conditions:**

- WHERE clause: WHERE level < 10

- No matching rows in JOIN

- MAXRECURSION hint (SQL Server)

Standard SQL feature since SQL:1999. Supported in PostgreSQL, MySQL 8.0+, SQL Server, Oracle, SQLite 3.8+. Essential for senior backend roles at FAANG companies. Used in 80% of enterprise applications with hierarchical data. Interview favorite for senior positions.

Recursive CTEs solve tree and graph problems that would require complex application code or multiple queries. Real examples: LinkedIn "People You May Know" (network traversal), Amazon category navigation (product hierarchy), Slack thread replies (nested comments), GitHub repository dependencies (dependency graphs). Every hierarchical structure in production databases uses recursive CTEs.