Recursive CTE for series generation in SQL

Introduction

We use recursive CTEs to create a list of numbers or dates step-by-step inside the database. This helps when you need a sequence of values without having a table ready.

You want to list all days in a month without a calendar table.

You need to generate numbers from 1 to 100 for testing or calculations.

You want to create a sequence of IDs or steps dynamically.

You want to fill missing dates in a report by generating all dates in a range.

Syntax

SQL

WITH RECURSIVE series_name(column_name) AS (
    -- Anchor member: starting point
    SELECT start_value
    UNION ALL
    -- Recursive member: adds next value
    SELECT column_name + step_value FROM series_name WHERE column_name < end_value
)
SELECT * FROM series_name;

The WITH RECURSIVE starts the recursive query.

The anchor member sets the first value in the series.

Examples

This generates numbers from 1 to 5.

SQL

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

This generates dates from January 1 to January 5, 2024.

SQL

WITH RECURSIVE dates(d) AS (
    SELECT DATE('2024-01-01')
    UNION ALL
    SELECT DATE(d, '+1 day') FROM dates WHERE d < DATE('2024-01-05')
)
SELECT * FROM dates;

This returns no rows because the anchor condition is false (start value 1 > 5).

SQL

WITH RECURSIVE empty_series(n) AS (
    SELECT 1 WHERE 1 > 5
    UNION ALL
    SELECT n + 1 FROM empty_series WHERE n < 5
)
SELECT * FROM empty_series;

Sample Program

This query creates a series of numbers from 1 to 10 using a recursive CTE.

SQL

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

Important Notes

Time complexity is roughly O(n) where n is the number of values generated.

Space complexity depends on how many rows the series produces; large series can use more memory.

Common mistake: forgetting the stopping condition in the recursive part causes infinite loops.

Use recursive CTEs when you don't have a ready table of numbers or dates and want to generate sequences on the fly.

Summary

Recursive CTEs help generate sequences step-by-step inside SQL.

Always include a stopping condition to avoid infinite loops.

Useful for creating number or date lists without extra tables.