SQL select number ranges I found it quite hard to achieve a range of numbers as rows in MySQL.

For example the range 1-5 is achieved by:

SELECT 1 
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5


will result in:


1
2
3
4
5



for 0-99 I can cross join two 0-9 tables:

CREATE TABLE nums as
SELECT 0 as num
UNION
SELECT 1 
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6 
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9
;

Select n.num*10+nums.num v 
From nums n cross join nums


I'm tired of writing all these UNIONs and looking for a way to shrink the code.

Any ideas how to golf it (for example 0-1,000,000 range) in MySQL or any SQL syntax ?

Extra points are given for:


single statement
no procedures
no variables
no DDL statements
only DQL statements
For SQL dialects that support recursive CTEs like sqlite, you can do something like the following:

WITH RECURSIVE f(x) AS
(
  SELECT 1 UNION ALL SELECT x + 1 FROM f LIMIT 1000000
)
SELECT x
FROM f;


This doesn't depend on any existing table and you can change the LIMIT clause as desired. I originally saw a variant of this on StackOverflow.