SQL: Select a list of numbers from "nothing"

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 Numberfrom 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

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

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

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.

Thanks for all answers!Following the discussion I realized that using a numbers table is not too complicated and works well and fast on both/many platforms:

CREATE TABLE integers (i integer);INSERT INTO integers (i) VALUES (0);INSERT INTO integers (i) VALUES (1);...INSERT INTO integers (i) VALUES (9);SELECT (hundreds.i * 100) + (tens.i * 10) + units.i AS xFROM integers AS units  CROSS JOIN integers AS tens  CROSS JOIN integers AS hundreds

You just create this table once and can use it whenever you need a range of numbers.