[Question]
  [
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 `UNION`s 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
  
[Answer]
For SQL dialects that support [recursive CTEs](https://sqlite.org/lang_with.html) 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.
[Answer]
Similar to [@BradC's method](https://codegolf.stackexchange.com/a/129221/61613). 
I used MS SQL, which has a table in `[master]` with a number range of -1 through 2048. You can use the `BETWEEN` operator to create your range. 
```
SELECT DISTINCT(number)
FROM master..[spt_values] 
WHERE number BETWEEN 1 AND 5
```
If you want to golf this, you can do:
```
SELECT TOP 5 ROW_NUMBER()OVER(ORDER BY number)FROM master..spt_values
```
[Answer]
### PostgreSQL, 35 bytes
PostgreSQL has this easy:
```
SELECT * FROM generate_series(1,5)
```
If you need it named:
```
SELECT num FROM generate_series(1,5)AS a(num)
```
You can also do this with timestamps. <https://www.postgresql.org/docs/9.5/static/functions-srf.html>
[Answer]
Great option [from this post](https://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql/21286493#21286493) (found by @Arnauld):
```
SELECT id%1000001 as num
FROM <any_large_table>
GROUP BY num
```
For me - it's pretty much solves the challenge.
[Answer]
# PostgreSQL specific
[`generate_series()`](https://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SERIES) generates a set, so you can user it not only in `from` clause, but anywhere where a set may occur:
```
psql=# select generate_series(10, 20, 3);
 generate_series 
-----------------
              10
              13
              16
              19
(4 rows)
```
You can also do operations directly on the set:
```
psql=# select 2000 + generate_series(10, 20, 3) * 2;
 ?column? 
----------
     2020
     2026
     2032
     2038
(4 rows)
```
If multiple sets have the same length, you can traverse them in parallel:
```
psql=# select generate_series(1, 3), generate_series(4, 6);
 generate_series | generate_series 
-----------------+-----------------
               1 |               4
               2 |               5
               3 |               6
(3 rows)
```
For sets with different lengths a Cartesian product is generated:
```
psql=# select generate_series(1, 3), generate_series(4, 5);
 generate_series | generate_series 
-----------------+-----------------
               1 |               4
               2 |               5
               3 |               4
               1 |               5
               2 |               4
               3 |               5
(6 rows)
```
But if you use them in `from` clause, you get Cartesian product for same length sets too:
```
psql=# select * from generate_series(1, 2), generate_series(3, 4) second;
 generate_series | second 
-----------------+--------
               1 |      3
               1 |      4
               2 |      3
               2 |      4
(4 rows)
```
It can also generate set of timestamps. For example you born on 2000-06-30 and want to know in which years you celebrated your birthday in a weekend:
```
psql=# select to_char(generate_series, 'YYYY - Day') from generate_series('2000-06-30', current_date, interval '1 year') where to_char(generate_series, 'D') in ('1', '7');
     to_char      
------------------
 2001 - Saturday 
 2002 - Sunday   
 2007 - Saturday 
 2012 - Saturday 
 2013 - Sunday   
(5 rows)
```
[Answer]
MS SQL has an undocumented system table in the `master` database called `spt_values`. Among other things, it contains a range of numbers from 0 to 2047:
```
--returns 0 to 2,047
SELECT number n 
FROM master..spt_values
WHERE TYPE='P'
```
Useful as a numbers table just by itself, but in a CTE you can get some big numbers pretty quickly:
```
--returns 0 to 4,194,304
WITH x AS(SELECT number n FROM master..spt_values WHERE TYPE='P')
SELECT 2048*x.a+*y.a
FROM x,x y
ORDER BY 1
```
[Answer]
(These work in MS-SQL, not sure if they works for mySQL or other platforms.)
For smaller sets (ordered or non-ordered), use the `VALUES` constructor:
```
--Generates 0-9
SELECT a 
FROM(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(a)
```
(This works for anything, although strings can get pretty long with all the repeated single quotes.)
Then you can cross-multiply using a named CTE (common table expression) so you don't have to repeat it:
```
--Generates 0-999
WITH x AS(SELECT a FROM(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(a))
SELECT 100*x.a+10*y.a+z.a 
FROM x,x y,x z
ORDER BY 1
```
There are tons of other techniques out there, look for "SQL generating a number table", although most aren't optimized for golfing.
[Answer]
One more option, this one specific to MS SQL 2016 and above:
```
SELECT value v
FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16', ',')
```
I'll likely find this more handy for lists of strings, but I can see ways it will be useful with numbers as well.
[Answer]
# T-SQL, 98 bytes
```
WITH H AS(SELECT 0i UNION ALL SELECT i+1FROM H WHERE i<99)SELECT H.i+1e4*A.i+B.i*1e2FROM H,H A,H B
```
* ✓ single statement
* ✓ no procedures
* ✓ no variables
* ✓ no DDL statements
* ✓ only DQL statements
[Answer]
Another for SQL Server...
```
WITH 
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100
    cte_Tally (n) AS (
        SELECT TOP (<how many ROWS do you want?>)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
            cte_n2 a CROSS JOIN cte_n2 b                                                    -- 10,000
        )
SELECT 
    t.n
FROM
    cte_Tally t;
```
]