SQL WITH RECURSIVE
A CTE that refers to itself, to generate series or walk hierarchies.
Syntax
WITH RECURSIVE r(n) AS ( SELECT start UNION ALL SELECT next FROM r WHERE … ) SELECT * FROM r;
The first SELECT is the starting point; the second runs repeatedly on the previous rows until it returns nothing.
Example
Query (on the shop database used in the SQL course)
WITH RECURSIVE months(m) AS ( SELECT '2025-01-01' UNION ALL SELECT date(m, '+1 month') FROM months WHERE m < '2025-06-01' ) SELECT strftime('%Y-%m', m) AS month FROM months;
Result
| month |
|---|
| 2025-01 |
| 2025-02 |
| 2025-03 |
| 2025-04 |
| 2025-05 |
| 2025-06 |
6 rows
Good to know. Always include a stopping condition.
Related
Learn it step by step in the free lesson CTEs & Window Functions, with exercises that check your queries.