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.
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.
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)
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 |
+---+---+---+---+
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 (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;
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