Sql to generate a list of numbers from 1 to 100

sql

best-practices

performance

join

byAlex Kataev·Dec 24, 2024

Generate a sequence from 1 to 100 in SQL using a Recursive CTE:

WITH Numbers AS (
    SELECT 1 AS Value -- Hey, everyone starts somewhere, right?
    UNION ALL
    SELECT Value + 1 FROM Numbers WHERE Value < 100 -- And then, the world! That is, until 100...
)
SELECT Value FROM Numbers OPTION (MAXRECURSION 100);

This concise script creates an on-the-fly number table using recursion, perfect for quick data sets.

Oracle-specific methods

Hierarchical method: CONNECT BY LEVEL

Oracle SQL provides an efficient method of generating series of numbers using CONNECT BY LEVEL.

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100;

Here, Oracle's in-build pseudo column LEVEL auto-increments for each iteration of our dummy table DUAL.

Recursive subquery: WITH clause

A powerful alternative in Oracle is the recursive WITH clause.

WITH Numbers (n) AS (
    SELECT 1 FROM DUAL -- Impressive. You're number one.
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n < 100 -- One by one, we count them all.
)
SELECT n FROM Numbers; -- Here's looking at you, kid. (1-100 kids, specifically).

It achieves the same as the CTE in our fast answer but is specialized for Oracle.

Special mentions

XMLTABLE: A distinctive approach

A distinctive method involves XMLTABLE:

SELECT * FROM XMLTABLE('for $i in 1 to 100 return $i');

This method uses the implied COLUMN_VALUE along with XML to create a number series anytime, anyplace!

MODEL clause: Structuring your numbers

With Oracle's MODEL clause, a series of numbers have never looked this good:

SELECT * FROM (
  SELECT 0 N FROM DUAL
) MODEL DIMENSION BY (N) MEASURES (N) RULES ITERATE(100) (N[ITERATION_NUMBER]=ITERATION_NUMBER+1);

The clause iterates to populate N with consecutive numbers from 1 to 100.

Dealing with different databases

PostgreSQL: The built-in saviour

In PostgreSQL, the built-in generate_series function makes it trivial:

SELECT * FROM generate_series(1, 100); -- From zero to hero? No, from 1 to 100!

MySQL: DUAL, the unsung hero

In MySQL, we can simulate a series using variables and good old DUAL:

SELECT @row := @row + 1 as Number -- @row takes one for the team, again and again!
FROM (
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
  ) t
CROSS JOIN (
  SELECT @row := 0 -- All I'm asking for is a chance, a fresh start!
) r
LIMIT 100;

SQL Server: Back to the basics

SQL Server, as demonstrated in our fast answer, can leverage Recursive CTEs to perfection. Just watch the MAXRECURSION!

Best practices for safeguarding your queries

Remember to:

Order your numbers with ORDER BY clause. Numbers love to stay in line!
Understand resource limits and performance trade-offs. Bigger isn't always better.
Always look for specific optimization techniques in your specific RDBMS. When in Rome...