VALUES Statement

In addition to being part of the INSERT statement, the VALUES clause can be used as stand-alone statement or with the SELECT statement to construct a data set without creating a table. For example, the following statement returns a data set of 2 rows and 3 columns.


VALUES ('r1_c1', 'r1_c2', 'r1_c3')
     , ('r2_c1', 'r2_c2', 'r2_c3');

Syntax:

VALUES (row)[, (row), ...];

SELECT select_list FROM (VALUES (row)[, (row), ...]) AS alias;

row ::= column [[AS alias], column [AS alias], ...]

The VALUES keyword is followed by a comma separated list of one or more rows.
row is a comma-separated list of one or more columns.
Each row must have the same number of columns.
column can be a constant, a variable, or an expression.
The corresponding columns must have compatible data types in all rows. See the third query in the Examples section below.
By default, the first row is used to name columns. But using the AS keyword, you can optionally give the column an alias.
If used in the SELECT statement, the AS keyword with an alias is required.
select_list is the columns to be selected for the result set.

Examples:

> SELECT * FROM (VALUES(4,5,6),(7,8,9)) AS t;
+---+---+---+
| 4 | 5 | 6 |
+---+---+---+
| 4 | 5 | 6 |
| 7 | 8 | 9 |
+---+---+---+

> SELECT * FROM (VALUES(1 AS c1, true AS c2, 'abc' AS c3),(100,false,'xyz')) AS t;
+-----+-------+-----+
| c1  | c2    | c3  |
+-----+-------+-----+
| 1   | true  | abc |
| 100 | false | xyz |
+-----+-------+-----+

> VALUES (CAST('2019-01-01' AS TIMESTAMP)), ('2019-02-02');
+---------------------------------+
| cast('2019-01-01' as timestamp) |
+---------------------------------+
| 2019-01-01 00:00:00             |
| 2019-02-02 00:00:00             |
+---------------------------------+

Related information:

SELECT Statement