SQL - how to create multi row results with no source table

Question

In standard SQL, is there a way to say:

select mubmle as x from mumblemmble

And get more than one line of results, like this

 x
 _ 
 1
 2
 3

without creating temporary tables? I can do it in SQL Server using row_count() if I know some table that has enough rows, like:

  select row_number() over (order by x.SomeColumn) from 
(select top 24 SomeColumn from TableThatHasAtLeast24Rows) x

But wonder if there's a standard (less dumb) way to do it.

Answer 1

There is no standard way, and no way at all in MySQL.

In Oracle:

SELECT  *
FROM    dual
CONNECT BY
        level < n

In MS SQL:

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < n
        )
SELECT  *
FROM    hier
OPTION (MAXRECURSION 0)

In PostgreSQL:

SELECT  *
FROM    generate_series (1, n)

Note that MS SQL, unlike Oracle, cannot swap recursion stack into temporary tablespace, so you may experience troubles when generating large datasets.

See this answer for more details

Answer 2

The SQL 2003 standard defines a way to do it - not all DBMS implement it, though:

<table value constructor> ::= VALUES <row value expression list>

<row value expression list> ::= <table row value expression>
                            [ { <comma> <table row value expression> }... ]

<row value expression> ::= 
         <row value special case>
     |   <explicit row value constructor>

<table row value expression> ::= 
         <row value special case>
     |   <row value constructor>

And, after wading through much other BNF, you can find:

<explicit row value constructor> ::= 
         <left paren> <row value constructor element> <comma>
                      <row value constructor element list> <right paren>
     |   ROW <left paren> <row value constructor element list> <right paren>
     |   <row subquery>

<row value constructor element list> ::= 
     <row value constructor element>
     [ { <comma> <row value constructor element> }... ]

<row value constructor element> ::= <value expression>

Which, when translated, means that in some contexts, you can use:

 VALUES (v1a, v1b, v1c), (v2a, v2b, v2c)

to create a table value with two rows and three columns in each row. The INSERT statement is one place you can use the construct. Another is in the FROM clause of a SELECT statement, though showing enough BNF to connect the dots would take more space than SO encourages for an answer.