How to select multiple rows filled with constants?

Question

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.

Answer 1

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 (this number coming from a parameter to the query).

Oracle:

SELECT  level
FROM    dual
CONNECT BY
        level <= :n

SQL Server 2008 through 2019:

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

PostgreSQL and SQL Server 2022+:

SELECT  l
FROM    generate_series(1, :n) l

Answer 2

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

SELECT constants 
FROM (VALUES 
         ('foo@gmail.com'), 
         ('bar@gmail.com'), 
         ('baz@gmail.com')
     ) AS MyTable(constants)

Where constants is the column name and MyTable is any appropriate name

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

Answer 3

In Oracle SQL you can use JSON_TABLE to create table from JSON:

SELECT
   t.emp_name,
   t.emp_age
FROM
   JSON_TABLE (
   '{
     "employees": [{name:"Alice", age: 19}, {name: "Bob", age: 23}]
  }',
  '$'
  COLUMNS (
     NESTED PATH '$.employees[*]'
     COLUMNS (
        emp_name VARCHAR2(50) PATH '$.name',
        emp_age NUMBER(3,0) PATH '$.age'
     )
  )
) t;

Answer 4

With SQLite3, the following select Go .QueryContext()

VALUES (1), (2)

Returns a single untyped column named “Column1” and two rows with values 1 and 2

VALUES is a special form of the simple SELECT statement https://www.sqlite.org/lang_select.html#simpleselect

Answer 5

The following bare VALUES command works for me in PostgreSQL:

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

Answer 6

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

Answer 7

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;

Answer 8

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

Answer 9

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 |
+---+---+---+---+

Answer 10

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.

Answer 11

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

Answer 12

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.

Answer 13

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
;

Answer 14

In Oracle

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

Answer 15

SELECT * 
FROM DUAL 
CONNECT BY ROWNUM <= 9;

Answer 16

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.

Answer 17

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