1
2
|
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
|
(
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:
1
2
3
4
5
6
7
8
9
10
11
|
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:
1
2
3
4
5
|
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”:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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:
1
2
3
4
5
6
|
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:
1
2
|
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:
1
2
3
4
|
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)
1
|
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 theCONNECT BY
clause, which is almost as convenient as PostgreSQL’s GENERATE_SERIES()
function:
1
2
|
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:
1
2
3
4
5
6
7
|
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!
1
2
3
4
5
6
7
8
9
10
|
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’sCONNECT BY
clause comes close. For all other databases, some trickery has to be applied in one way or another.
https://blog.jooq.org/2013/11/19/how-to-create-a-range-from-1-to-10-in-sql/