VALUES Clause
Description
The VALUES clause is used to construct inline row data. It can be used as a standalone query or as a temporary table in the FROM clause. It is commonly used for quick testing, constructing sample data, or inserting data together with INSERT INTO.
Syntax
-- Standalone usage
VALUES (expr1 [, expr2, ...]) [, (expr1 [, expr2, ...]), ...]
-- Used in FROM clause, specifying table alias and column names
SELECT ...
FROM VALUES (expr1 [, expr2, ...]) [, ...] AS table_alias(col1 [, col2, ...])
-- Short form (omitting the VALUES keyword)
SELECT ...
FROM (expr1 [, expr2, ...]) [, ...] AS table_alias(col1 [, col2, ...])
Parameters
expr: Any expression, which can be a literal, function call, or computed expression.table_alias: The alias for the temporary table.col1, col2, ...: The aliases for the columns.
Examples
Standalone Query
VALUES (1, 'a'), (2, 'b'), (3, 'c');
+------+------+
| col1 | col2 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
Using in FROM Clause
SELECT id, name
FROM VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie') AS t(id, name);
+----+---------+
| id | name |
+----+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
+----+---------+
Using with Aggregate Functions
SELECT sum(val) AS total
FROM VALUES (10), (20), (30) AS t(val);
+-------+
| total |
+-------+
| 60 |
+-------+
Multiple Columns of Different Types
SELECT *
FROM VALUES
(1, 'hello', date '2024-01-01', true),
(2, 'world', date '2024-06-15', false)
AS t(id, msg, dt, flag);
+----+-------+------------+-------+
| id | msg | dt | flag |
+----+-------+------------+-------+
| 1 | hello | 2024-01-01 | true |
| 2 | world | 2024-06-15 | false |
+----+-------+------------+-------+
Using with INSERT INTO
INSERT INTO my_table
VALUES (1, 'Alice'), (2, 'Bob');
Notes
- The number of columns in each row must be consistent.
- The types of columns in corresponding positions across rows must be compatible; the system will automatically infer the common type.
- When using in the FROM clause, it is recommended to specify column names via
AS table_alias(col1, col2, ...), otherwise column names default to col1, col2, etc.
- VALUES can be combined with UNION, JOIN, and other operations.
