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:
This was easy, right? Things even look more lean when using functional programming. Take Scala, for instance:
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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.
