[sql] How to select multiple rows filled with constants?

Selecting constants without referring to a table is perfectly legal in an SQL statement:

SELECT 1, 2, 3

The result set that the latter returns is a single row containing the values. I was wondering if there is a way to select multiple rows at once using a constant expression, something kind of:

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

I would want something like the above that works and returns a result set with 3 rows and 3 columns.

Here is how to do it using the XML features of DB2

SELECT *
FROM
XMLTABLE ('$doc/ROWSET/ROW' PASSING XMLPARSE ( DOCUMENT '
<ROWSET>
  <ROW>
    <A val="1" /> <B val="2" /> <C val="3" />
  </ROW>
  <ROW>
    <A val="4" /> <B val="5" /> <C val="6" />
  </ROW>
  <ROW>
    <A val="7" /> <B val="8" /> <C val="9" />
  </ROW>
</ROWSET>
') AS "doc"
   COLUMNS 
      "A" INT PATH 'A/@val',
      "B" INT PATH 'B/@val',
      "C" INT PATH 'C/@val'
) 
AS X
;

The following bare VALUES command works for me in PostgreSQL:

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

This way can help you

SELECT   TOP 3
         1 AS First, 
         2 AS Second, 
         3 AS Third 
FROM     Any_Table_In_Your_DataBase

Any_Table_In_Your_DataBase: any table which contains more than 3 records, or use any system table. Here we have no concern with data of that table.

You can bring variations in result set by concatenating a column with First, Second and Third columns from Any_Table_In_Your_DataBase table.

Oracle. Thanks to this post PL/SQL - Use "List" Variable in Where In Clause

I put together my example statement to easily manually input values (being reused in testing an application by testers):

WITH prods AS (
    SELECT column_value AS prods_code 
    FROM TABLE(
        sys.odcivarchar2list(
            'prod1', 
            'prod2'
        )
    )
)
SELECT * FROM prods

Here a way to create custom rows directly with MySQL request SELECT :

SELECT ALL *
FROM (
    VALUES
        ROW (1, 2, 3),
        ROW (4, 5, 6),
        ROW (7, 8, 9)
) AS dummy (c1, c2, c3)

Gives us a table dummy :

c1   c2   c3
-------------
 1    2    3
 4    5    6
 7    8    9

Tested with MySQL 8

SELECT * 
FROM DUAL 
CONNECT BY ROWNUM <= 9;

An option for DB2:

SELECT 101 AS C1, 102 AS C2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 201 AS C1, 202 AS C2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 301 AS C1, 302 AS C2 FROM SYSIBM.SYSDUMMY1

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.

In Oracle

SELECT
  CASE
    WHEN level = 1
    THEN 'HI'
    WHEN level = 2
    THEN 'BYE'
  END TEST
FROM dual
  CONNECT BY level <= 2;

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

In MySQL, you can do: values (1,2), (3, 4);

mysql> values (1,2), (3, 4);
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+
2 rows in set (0.004 sec)

With MySQL 8, it is also possible to give the column names:

mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+

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.

select (level - 1) * row_dif + 1 as a, (level - 1) * row_dif + 2 as b, (level - 1) * row_dif + 3 as c
    from dual 
    connect by level <= number_of_rows;

something like that

select (level - 1) * 3 + 1 as a, (level - 1) * 3 + 2 as b, (level - 1) * 3 + 3 as c
    from dual 
    connect by level <= 3;

Here is how I populate static data in Oracle 10+ using a neat XML trick.

create table prop
(ID NUMBER,
 NAME varchar2(10),
 VAL varchar2(10),
 CREATED timestamp,
 CONSTRAINT PK_PROP PRIMARY KEY(ID)
);

merge into Prop p
using (
select 
  extractValue(value(r), '/R/ID') ID,
  extractValue(value(r), '/R/NAME') NAME,
  extractValue(value(r), '/R/VAL') VAL
from
(select xmltype('
<ROWSET>
   <R><ID>1</ID><NAME>key1</NAME><VAL>value1</VAL></R>
   <R><ID>2</ID><NAME>key2</NAME><VAL>value2</VAL></R>
   <R><ID>3</ID><NAME>key3</NAME><VAL>value3</VAL></R>
</ROWSET>
') xml from dual) input,
 table(xmlsequence(input.xml.extract('/ROWSET/R'))) r
) p_new
on (p.ID = p_new.ID)
when not matched then
insert
(ID, NAME, VAL, CREATED)
values
( p_new.ID, p_new.NAME, p_new.VAL, SYSTIMESTAMP );

The merge only inserts the rows that are missing in the original table, which is convenient if you want to rerun your insert script.