Apache Impala SQL ReferencePDF version

VALUES statement

The VALUES clause can be used as stand-alone statement, in the INSERT statement, and in the SELECT statement to construct a data set. 

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