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.

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

Syntax:

  1. VALUES (row)[, (row), ...];
  3. SELECT select_list FROM (VALUES (row)[, (row), ...]) AS alias;
  5. 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:

  1. > SELECT * FROM (VALUES(4,5,6),(7,8,9)) AS t;
  2. +---+---+---+
  3. | 4 | 5 | 6 |
  4. +---+---+---+
  5. | 4 | 5 | 6 |
  6. | 7 | 8 | 9 |
  7. +---+---+---+
  9. > SELECT * FROM (VALUES(1 AS c1, true AS c2, 'abc' AS c3),(100,false,'xyz')) AS t;
  10. +-----+-------+-----+
  11. | c1  | c2    | c3  |
  12. +-----+-------+-----+
  13. | 1   | true  | abc |
  14. | 100 | false | xyz |
  15. +-----+-------+-----+
  17. > VALUES (CAST('2019-01-01' AS TIMESTAMP)), ('2019-02-02');
  18. +---------------------------------+
  19. | cast('2019-01-01' as timestamp) |
  20. +---------------------------------+
  21. | 2019-01-01 00:00:00             |
  22. | 2019-02-02 00:00:00             |
  23. +---------------------------------+

Related information:

SELECT Statement

Parent topic: Impala SQL Statements