Categories:

Query syntax

VALUES

In the SELECT statement, the VALUES sub-clause of the FROM clause allows the specification of a set of constants to be used to form a finite set of rows.

(For information about the VALUES clause in the INSERT statement, see the documentation of the INSERT statement.)

Syntax

SELECT ...
FROM ( VALUES ( <expr> [ , <expr> [ , ... ] ] ) [ , ( ... ) ] ) [ [ AS ] <table_alias> [ ( <column_alias> [, ... ] ) ] ]
[ ... ]
Copy

Parameters

expr

Each expression must be a constant, or an expression that can be evaluated as a constant during compilation of the SQL statement.

Most simple arithmetic expressions and string functions can be evaluated at compile time, but most other expressions cannot.

table_alias

An optional alias can be used to give the set of rows a name, as though the set of rows were a table.

column_alias

Optional column aliases can be used to give the columns names.

Usage notes

  • Inside a FROM clause, a VALUES clause cannot contain the DEFAULT keyword. This is in contrast to a VALUES clause in an INSERT statement, which supports the use of DEFAULT (e.g. INSERT INTO table VALUES (10, DEFAULT, 'Name') ...).

  • The VALUES clause is limited to 16,384 rows.

Examples

The VALUES clause is useful for generating a fixed, known set of rows:

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three'));

+---------+---------+
| COLUMN1 | COLUMN2 |
|---------+---------|
|       1 | one     |
|       2 | two     |
|       3 | three   |
+---------+---------+

SELECT column1, $2 FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three'));

+---------+-------+
| COLUMN1 | $2    |
|---------+-------|
|       1 | one   |
|       2 | two   |
|       3 | three |
+---------+-------+
Copy

Note that the values can be referenced either by column “name” (implicit) or column position.

If your query uses multiple VALUES clauses, you may use an alias to distinguish between them, for example:

SELECT v1.$2, v2.$2
  FROM (VALUES (1, 'one'), (2, 'two')) AS v1
        INNER JOIN (VALUES (1, 'One'), (3, 'three')) AS v2
  WHERE v2.$1 = v1.$1;
Copy

You can also specify aliases for the column names:

SELECT c1, c2
  FROM (VALUES (1, 'one'), (2, 'two')) AS v1 (c1, c2);
Copy