Why Recursive CTE for series generation in SQL? - Purpose & Use Cases

The Big Idea

What if you could create endless lists of numbers with just one simple query?

The Scenario

Imagine you need to create a list of numbers from 1 to 100 for a report, but you only have a basic table with a few rows. You try to write out each number manually or copy-paste them one by one.

The Problem

Writing out each number by hand is slow and boring. Copy-pasting can cause mistakes like missing numbers or duplicates. If you want a longer list or a different range, you have to redo everything. It's easy to get tired and make errors.

The Solution

Using a Recursive CTE lets you generate a series of numbers automatically inside your SQL query. It starts from a base number and keeps adding the next number until it reaches the limit you set. This saves time, avoids mistakes, and works for any range you want.

Before vs After

✗ Before

SELECT 1 AS number UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL ...

✓ After

WITH RECURSIVE numbers AS (SELECT 1 AS number UNION ALL SELECT number + 1 FROM numbers WHERE number < 100) SELECT number FROM numbers;

What It Enables

You can quickly create any sequence of numbers or dates inside your database, making complex reports and calculations much easier.

Real Life Example

A sales manager wants to see daily sales for the past month, even on days with no sales recorded. Using a recursive CTE to generate all dates in the month ensures every day appears in the report.

Key Takeaways

Manual number lists are slow and error-prone.

Recursive CTEs generate sequences automatically and flexibly.

This technique helps create complete reports and data sets easily.