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.