How to select multiple rows filled with constants?

Question

SELECT 1, 2, 3
UNION ALL SELECT 4, 5, 6
UNION ALL SELECT 7, 8, 9

In PostgreSQL, you can do:

SELECT  *
FROM    (
        VALUES
        (1, 2),
        (3, 4)
        ) AS q (col1, col2)

In other systems, just use UNION ALL:

SELECT  1 AS col1, 2 AS col2
-- FROM    dual
-- uncomment the line above if in Oracle
UNION ALL
SELECT  3 AS col1, 3 AS col2
-- FROM    dual
-- uncomment the line above if in Oracle

In Oracle, SQL Server and PostgreSQL, you also can generate recordsets of arbitrary number of rows (providable with an external variable):

SELECT  level
FROM    dual
CONNECT BY
        level <= :n

in Oracle,

WITH    q (l) AS
        (
        SELECT  1
        UNION ALL
        SELECT  l + 1
        FROM    q
        WHERE   l < @n
        )
SELECT  l
FROM    q
-- OPTION (MAXRECURSION 0)
-- uncomment line above if @n >= 100

in SQL Server,

SELECT  l
FROM    generate_series(1, $n) l

in PostgreSQL.

The following bare VALUES command works for me in PostgreSQL:

VALUES (1,2,3), (4,5,6), (7,8,9)

For Microsoft SQL Server or PostgreSQL you may want to try this syntax

SELECT constants FROM (VALUES ('[email protected]'), ('[email protected]'), ('[email protected]')) AS MyTable(constants)

You can also view an SQL Fiddle here: http://www.sqlfiddle.com/#!17/9eecb/34703/0

Try the connect by clause in oracle, something like this

select level,level+1,level+2 from dual connect by level <=3;

For more information on connect by clause follow this link : removed URL because oraclebin site is now malicious.