SQL: Select a list of numbers from "nothing"

Question

What is a fast/readable way to SELECT a relation from "nothing" that contains a list of numbers. I want to define which numbers by setting a start and end value. I am using Postgres SQL and SQLite, and would be interested in generic solutions that will work on both/many platforms.

Desired output relation:

# x
  0
  1
  2
  3
  4

I know that I can SELECT a single row from "nothing": SELECT 0,1,2,3,4 But this selects the values as columns instead of rows and requires to specify all values in the query instead of only using my start and end values: 0 and 4.

In Postgres you have a special generate_series function for this case:

SELECT * FROM generate_series(0,4) x;

This works nicely but is non-standard. I can also imagine some complicated solutions using temporary tables, but I would like to have something generic AND simple like:

SELECT * FROM [0..4]

Maybe using the SEQUENCE statement or some magic combination of SELECT 0 and SELECT 4?

Answer 1

Well in SQL server (and PostgreSQL) I would use recursive common table expression: SQL Server, PostgreSQL

with recursive Numbers as (
    select 0 as Number
    union all
    select Number + 1
    from Numbers
    where Number < 4
)
select Number
from Numbers

SQL FIDDLE EXAMPLE

But, as far as I know, there's no WITH in SQLite.

So, the possible solutions could be

• create a user defined function (this could be helpful)

• create a table with numbers from 0 to max number you'll ever need, and then just select from it like this:

  select Number from Numbers where Number >= 0 and Number <= 4
  

Answer 2

A simple way to do this in PostgreSQL and SQLite is as follows:

sqlite> select 1 union select 2 union select 3;
1
2
3

It should work in most RDBMS systems, but IIRC in Oracle you would have to use:

select 1 from dual union select 2 from dual union select 3 from dual;

But I don't have an Oracle DB to test it on.