Purpose
This statement is used to return a set of one or more rows of data as a table.
VALUES is a table-valued constructor and can also be used as an independent SQL statement in combination with operators such as 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
Parameters
Parameter |
Description |
|---|---|
| row_constructor_list | The list of row constructors, consisting of ROW() Row constructor clauses, separated by commas. ROW() cannot be empty, but each scalar value within the parentheses can be NULL. |
| value_list | A list of zero or more scalar values. The scalar values can be literals or expressions that are parsed as scalar values. The same VALUES Each ROW() Included in value_list must have the same number of values. |
| column_designator | Specifies a column. The column name must follow these rules: column_0, column_1, .... The optional ORDER BY clause sorts the rows by the specified column. |
Examples
The
VALUESoutput is a table sorted by the specified columncolumn_0.obclient> VALUES ROW(4,6,8), ROW(5,7,9), ROW(1,-2,3) ORDER BY column_0;The output result of the example is as follows:
+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | -2 | 3 | | 4 | 6 | 8 | | 5 | 7 | 9 | +----------+----------+----------+ 3 rows in setThe
VALUESstatement supports mixed data types for data in the same column.obclient> VALUES ROW("q", 23, '2022-12-18'), ROW(87, "x+z", 92.6), ROW(25.0009, "Well Smith", '{"a": 11, "b": 55}');The output of the sample code 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} | +----------+------------+--------------------+When used with
UNION,VALUESis equivalent toSELECT.obclient> SELECT 11,22 UNION SELECT 33,44;The output result of the example is as follows:
+------+------+ | 11 | 22 | +------+------+ | 11 | 22 | | 33 | 44 | +------+------+ 2 rows in setobclient> VALUES ROW(11,22) UNION VALUES ROW(33,44);The output result of the example is as follows:
+----------+----------+ | column_0 | column_1 | +----------+----------+ | 11 | 22 | | 33 | 44 | +----------+----------+ 2 rows in set