VALUES

Purpose

You can use this statement to return a set of one or more rows as a table.

The VALUES statement is a table value constructor, which can be used independently or together with UNION, EXCEPT, INTERSECT, and so on.

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

Simple examples

• Use VALUES to generate a table where rows are sorted by the column_0 column.

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

  The output of the example is as follows:

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

• Use VALUES to generate a table where a column contains data of mixed types.

  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 example 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 together with UNION, which is equivalent to 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 of the example is as follows:

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

Application examples

• Use the VALUES statement instead of multiple UNION ALL operations:

To improve the efficiency of creating temporary table data, you can use the VALUES statement instead of multiple UNION ALL operations. The following shows how to use the VALUES statement to represent temporary table data:

Original SQL example using UNION ALL:

SELECT 1, 2, 3 UNION ALL SELECT 4, 5, 6 UNION ALL SELECT 7, 8, 9;

The result is shown below:

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

Optimized SQL using the VALUES statement:

VALUES ROW(1, 2, 3), ROW(4, 5, 6), ROW(7, 8, 9);

The result is shown below:

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

In addition, when temporary table data is needed in joins or conditional branches, the VALUES statement also demonstrates its convenience and efficiency:

Original SQL using UNION ALL:

CREATE TABLE t1 (type_a int, type_b int, type_c int);
SELECT CASE WHEN V.c1 = 1 AND t1.type_a = 1 THEN 'type_a'
            WHEN V.c1 = 2 AND t1.type_b = 1 THEN 'type_b'
            WHEN V.c1 = 3 AND t1.type_c = 1 THEN 'type_c'
            ELSE NULL
       END AS col_type
       FROM t1, (SELECT 1 AS c1 UNION ALL SELECT 2 UNION ALL SELECT 3) V;

Optimized SQL using the VALUES statement:

SELECT CASE WHEN V.column_0 = 1 AND t1.type_a = 1 THEN 'type_a'
            WHEN V.column_0 = 2 AND t1.type_b = 1 THEN 'type_b'
            WHEN V.column_0 = 3 AND t1.type_c = 1 THEN 'type_c'
            ELSE NULL
       END AS col_type
       FROM t1, (VALUES ROW(1), ROW(2), ROW(3)) AS V(column_0);

• Use the VALUES statement to quickly create tables or views:

SQL example for creating a table quickly:

CREATE TABLE t1 AS VALUES ROW(1, 2, 3), ROW(4, 5, 6), ROW(7, 8, 9);

The result is shown below:

obclient> select * from t1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|        4 |        5 |        6 |
|        7 |        8 |        9 |
+----------+----------+----------+
3 rows in set

SQL example for creating a view quickly:

CREATE VIEW v1 AS VALUES ROW(1, 2, 3), ROW(4, 5, 6), ROW(7, 8, 9);

The result is shown below:

obclient > select * from v1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|        4 |        5 |        6 |
|        7 |        8 |        9 |
+----------+----------+----------+
3 rows in set