How to Create a Range From 1 to 10 in SQL

How do you create a range from 1 to 10 in SQL? Have you ever thought about it? This is such an easy problem to solve in any imperative language, it’s ridiculous. Take Java (or C, whatever) for instance:

for (int i = 1; i <= 10; i++)
  System.out.println(i);

This was easy, right? Things even look more lean when using functional programming. Take Scala, for instance:

(1 to 10) foreach { t => println(t) }

We could fill about 25 pages about various ways to do the above in Scala, agreeing on how awesome Scala is (or what hipsters we are).

But how to create a range in SQL?

… And we’ll exclude using stored procedures, because that would be no fun. In SQL, the data source we’re operating on are tables. If we want a range from 1 to 10, we’d probably need a table containing exactly those ten values. Here are a couple of good, bad, and ugly options of doing precisely that in SQL. OK, they’re mostly bad and ugly.

By creating a table

The dumbest way to do this would be to create an actual temporary table just for that purpose:

CREATE TABLE "1 to 10" AS
SELECT 1 value FROM DUAL UNION ALL
SELECT 2       FROM DUAL UNION ALL
SELECT 3       FROM DUAL UNION ALL
SELECT 4       FROM DUAL UNION ALL
SELECT 5       FROM DUAL UNION ALL
SELECT 6       FROM DUAL UNION ALL
SELECT 7       FROM DUAL UNION ALL
SELECT 8       FROM DUAL UNION ALL
SELECT 9       FROM DUAL UNION ALL
SELECT 10      FROM DUAL

This table can then be used in any type of select. Now that’s pretty dumb but straightforward, right? I mean, how many actual records are you going to put in there?

By using a VALUES() table constructor

This solution isn’t that much better. You can create a derived table and manually add the values from 1 to 10 to that derived table using the VALUES() table constructor. In SQL Server, you could write:

SELECT V
FROM (
  VALUES (1), (2), (3), (4), (5), 
         (6), (7), (8), (9), (10)
) [1 to 10](V)

By creating enough self-joins of a sufficent number of values

Another “dumb”, yet a bit more generic solution would be to create only a certain amount of constant values in a table, view or CTE (e.g. two) and then self join that table enough times to reach the desired range length (e.g. four times). The following example will produce values from 1 to 10, “easily”:

WITH T(V) AS (
  SELECT 0 FROM DUAL UNION ALL
  SELECT 1 FROM DUAL
)
SELECT V FROM (
  SELECT 1        + 
             T1.V + 
         2 * T2.V + 
         4 * T3.V + 
         8 * T4.V V
  FROM T T1, T T2, T T3, T T4
)
WHERE V <= 10
ORDER BY V

By using grouping sets

Another way to generate large tables is by using grouping sets, or more specifically by using the CUBE() function. This works much in a similar way as the previous example when self-joining a table with two records:

SELECT ROWNUM FROM (
  SELECT 1
  FROM DUAL
  GROUP BY CUBE(1, 2, 3, 4)
)
WHERE ROWNUM <= 10

By just taking random records from a “large enough” table

In Oracle, you could probably use ALL_OBJECTs. If you’re only counting to 10, you’ll certainly get enough results from that table:

SELECT ROWNUM FROM ALL_OBJECTS
WHERE ROWNUM <= 10

What’s so “awesome” about this solution is that you can cross join that table several times to be sure to get enough values:

SELECT ROWNUM 
FROM ALL_OBJECTS, ALL_OBJECTS,
     ALL_OBJECTS, ALL_OBJECTS
WHERE ROWNUM <= 10

OK. Just kidding. Don’t actually do that. Or if you do, don’t blame me if your productive system runs low on memory.

By using the awesome PostgreSQL GENERATE_SERIES() function

Incredibly, this isn’t part of the SQL standard. Neither is it available in most databases but PostgreSQL, which has the GENERATE_SERIES() function. This is much like Scala’s range notation: (1 to 10)

SELECT * FROM GENERATE_SERIES(1, 10)

By using CONNECT BY

If you’re using Oracle, then there’s a really easy way to create such a table using the CONNECT BY clause, which is almost as convenient as PostgreSQL’s GENERATE_SERIES() function:

SELECT LEVEL FROM DUAL
CONNECT BY LEVEL < 10

By using a recursive CTE

Recursive common table expressions are cool, yet utterly unreadable. the equivalent of the above Oracle CONNECT BY clause when written using a recursive CTE would look like this:

WITH "1 to 10"(V) AS (
  SELECT 1 FROM DUAL
  UNION ALL
  SELECT V + 1 FROM "1 to 10"
  WHERE V < 10
)
SELECT * FROM "1 to 10"

By using Oracle’s MODEL clause

A decent “best of” comparison of how to do things in SQL wouldn’t be complete without at least one example using Oracle’s MODEL clause (see this awesome use-case for Oracle’s spreadsheet feature). Use this clause only to make your co workers really angry when maintaining your SQL code.

Bow before this beauty!

SELECT V
FROM (
  SELECT 1 V FROM DUAL
) T
MODEL DIMENSION BY (ROWNUM R)
      MEASURES (V)
      RULES ITERATE (10) (
        V[ITERATION_NUMBER] = CV(R) + 1
      )
ORDER BY 1

Conclusion

There aren’t actually many nice solutions to do such a simple thing in SQL. Clearly, PostgreSQL’s GENERATE_SERIES() table function is the most beautiful solution. Oracle’s CONNECT BY clause comes close. For all other databases, some trickery has to be applied in one way or another.

Unfortunately.