VALUES

Purpose

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

VALUES is a table-valued 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

Parameters

Examples

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

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

  The output is as follows:

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

• The VALUES statement supports mixed data types 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 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} |
  +----------+------------+--------------------+
  

• VALUES can be used with UNION to achieve the same result as SELECT.

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

  The output is as follows:

  +----------+----------+
  | column_0 | column_1 |
  +----------+----------+
  |       11 |       22 |
  |       33 |       44 |
  +----------+----------+
  2 rows in set