Generating a Range of Numbers Between Two Values in SQL

1. Introduction

In SQL databases, dynamically generating a sequence of numbers is very useful for tasks like populating tables, creating test data, or performing iterative calculations. This capability streamlines many operations and is supported by different SQL platforms, including PostgreSQL, MySQL, and SQL Server.

In this tutorial, we’ll discuss logic to generate a range of values in PostgreSQL, MySQL, and SQL Server.

2. Generation of a Range of Numbers

2.1. PostgreSQL

In PostgreSQL, we can use the generate_series() function which is convenient for generating a range of numbers. The basic syntax is:

generate_series(start, stop, step)

The start argument is the beginning of the value range, the stop argument is the ending value of the range, and the step argument (with the default value being 1) is the increment for each value in the range.

Let’s generate a range of numbers 1 to 10 using the default step of 1:

SELECT generate_series(1, 10) AS numbers;

As we can see, the query works as expected:

numbers
---------
1
2
3
4
5
6
7
8
9
10
(10 rows)

2.2. MySQL

In MySQL (version 8.0 and higher) we can use recursive CTE (common table expression) to generate a range of values:

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

In the above example, WITH RECURSIVE is used for naming the CTE. SELECT 5 AS number is used as a base query on top of which we’ll build recursion. SELECT number + 1 FROM numbers WHERE number < 10 is used for adding 1 to the value of the previous iteration and continuing until the number reaches 10.

numbers
---------
1
2
3
4
5
6
7
8
9
10
(10 rows)

As we can see, the query works as expected.

2.3. SQL Server

SQL Server also supports recursive CTE and we can use it for generating a range of values. But the syntax has subtle differences. Unlike MySQL, we do not need to use RECURSIVE keyword in SQL Server:

WITH number_range AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1 FROM number_range WHERE number < 10
)
SELECT number FROM number_range;

Executing the query generates numbers from 1 to 10:

numbers
---------
1
2
3
4
5
6
7
8
9
10
(10 rows)

As we can see, the query works as expected.

3. Conclusion

In summary, generating numeric ranges in SQL is crucial for many database tasks and can be achieved through various approaches across various databases. Whether we use the built-in function in PostgreSQL or recursive CTE in MySQL and SQL Server, understanding these approaches enhances the flexibility and performance of our queries.