## 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
```

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.