VALUES

Description

This statement is used to return a set of single or multiple rows of data as a table.

VALUES is a table-value constructor that can also be used as a standalone SQL statement in conjunction with UNION, EXCEPT, and INTERSECT.

Syntax

VALUES row_constructor_list [ORDER BY column_designator] [LIMIT number]

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

value_list:
    value[, value][, ...]

column_designator:
    column_index

Parameter Description

Parameter	Description
row_constructor_list	A list of row constructors, composed of ROW() clauses separated by commas. ROW() cannot be empty, but each scalar value enclosed in parentheses can be NULL.
value_list	A list of one or more scalar values. Scalar values can be literals or expressions that resolve to scalar values. Each ROW() in the same VALUES statement must contain the same number of values in the value_list.
column_designator	Specifies a column, named column_0, column_1, etc. Use the optional ORDER BY clause to sort the rows by this specified column.

Examples

• Use VALUES to output a table and sort by the specified column column_0.

  VALUES ROW(4,6,8), ROW(5,7,9), ROW(1,-2,3) ORDER BY column_0;

  The output result is as follows:

  +----------+----------+----------+
  | column_0 | column_1 | column_2 |
  +----------+----------+----------+
  |        1 |       -2 |        3 |
  |        4 |        6 |        8 |
  |        5 |        7 |        9 |
  +----------+----------+----------+
  3 rows in set (0.012 sec)

• The VALUES statement supports mixed data types in the same column.

  VALUES ROW("q", 23, '2022-12-18'),  ROW(87, "x+z", 92.6), ROW(25.0009, "Well Smith", '{"a": 11, "b": 55}');

  The output result is as follows:

  +----------+------------+--------------------+
  | column_0 | column_1   | column_2           |
  +----------+------------+--------------------+
  | q        | 23         | 2022-12-18         |
  | 87       | x+z        | 92.6               |
  | 25.0009  | Well Smith | {"a": 11, "b": 55} |
  +----------+------------+--------------------+

• Use VALUES with UNION, which is equivalent to SELECT.

  SELECT 11,22 UNION SELECT 33,44;
  +------+------+
  | 11   | 22   |
  +------+------+
  |   11 |   22 |
  |   33 |   44 |
  +------+------+
  2 rows in set (0.023 sec)
  
  VALUES ROW(11,22) UNION VALUES ROW(33,44);

  The output result is as follows:

  +----------+----------+
  | column_0 | column_1 |
  +----------+----------+
  |       11 |       22 |
  |       33 |       44 |
  +----------+----------+
  2 rows in set (0.011 sec)