Recursive CTEs

WITH RECURSIVE lets a CTE reference itself, enabling loops in SQL.

Generate numbers 1 through 10:

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

How it works:

1. Base case: SELECT 1 AS n (the starting point)
2. Recursive step: SELECT n + 1 FROM nums WHERE n < 10
3. Stops when the WHERE condition is false

Real-world uses:

• Traversing org chart hierarchies
• Bill of materials (parts within parts)
• Generating date series