Home / SQL / Reference / Subqueries, CTEs and sets

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.