VALUES¶

Syntax¶

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

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