values - ClickHouse Documentation

The Values table function allows you to create temporary storage which fills columns with values. It is useful for quick testing or generating sample data.

Values is a case-insensitive function. I.e. VALUES or values are both valid.

Syntax

The basic syntax of the VALUES table function is:

VALUES([structure,] values...)

It is commonly used as:

VALUES(
    ['column1_name Type1, column2_name Type2, ...'],
    (value1_row1, value2_row1, ...),
    (value1_row2, value2_row2, ...),
    ...
)

Arguments

column1_name Type1, ... (optional). String specifying the column names and types. If this argument is omitted columns will be named as c1, c2, etc.
(value1_row1, value2_row1). Tuples containing values of any type.

Comma separated tuples can be replaced by single values as well. In this case each value is taken to be a new row. See the examples section for details.

Returned value

Returns a temporary table containing the provided values.

Examples

Query

SELECT *
FROM VALUES(
    'person String, place String',
    ('Noah', 'Paris'),
    ('Emma', 'Tokyo'),
    ('Liam', 'Sydney'),
    ('Olivia', 'Berlin'),
    ('Ilya', 'London'),
    ('Sophia', 'London'),
    ('Jackson', 'Madrid'),
    ('Alexey', 'Amsterdam'),
    ('Mason', 'Venice'),
    ('Isabella', 'Prague')
)

Response

    ┌─person───┬─place─────┐
│ Noah     │ Paris     │
│ Emma     │ Tokyo     │
│ Liam     │ Sydney    │
│ Olivia   │ Berlin    │
│ Ilya     │ London    │
│ Sophia   │ London    │
│ Jackson  │ Madrid    │
│ Alexey   │ Amsterdam │
│ Mason    │ Venice    │
│ Isabella │ Prague    │
    └──────────┴───────────┘

VALUES can also be used with single values rather than tuples. For example:

Query

SELECT *
FROM VALUES(
    'person String',
    'Noah',
    'Emma',
    'Liam',
    'Olivia',
    'Ilya',
    'Sophia',
    'Jackson',
    'Alexey',
    'Mason',
    'Isabella'
)

Response

    ┌─person───┐
│ Noah     │
│ Emma     │
│ Liam     │
│ Olivia   │
│ Ilya     │
│ Sophia   │
│ Jackson  │
│ Alexey   │
│ Mason    │
│ Isabella │
    └──────────┘

Or without providing a row specification ('column1_name Type1, column2_name Type2, ...' in the syntax), in which case the columns are automatically named. For example:

Query

-- tuples as values
SELECT *
FROM VALUES(
    ('Noah', 'Paris'),
    ('Emma', 'Tokyo'),
    ('Liam', 'Sydney'),
    ('Olivia', 'Berlin'),
    ('Ilya', 'London'),
    ('Sophia', 'London'),
    ('Jackson', 'Madrid'),
    ('Alexey', 'Amsterdam'),
    ('Mason', 'Venice'),
    ('Isabella', 'Prague')
)

Response

    ┌─c1───────┬─c2────────┐
│ Noah     │ Paris     │
│ Emma     │ Tokyo     │
│ Liam     │ Sydney    │
│ Olivia   │ Berlin    │
│ Ilya     │ London    │
│ Sophia   │ London    │
│ Jackson  │ Madrid    │
│ Alexey   │ Amsterdam │
│ Mason    │ Venice    │
│ Isabella │ Prague    │
    └──────────┴───────────┘

Query

-- single values
SELECT *
FROM VALUES(
    'Noah',
    'Emma',
    'Liam',
    'Olivia',
    'Ilya',
    'Sophia',
    'Jackson',
    'Alexey',
    'Mason',
    'Isabella'
)

Response

    ┌─c1───────┐
│ Noah     │
│ Emma     │
│ Liam     │
│ Olivia   │
│ Ilya     │
│ Sophia   │
│ Jackson  │
│ Alexey   │
│ Mason    │
│ Isabella │
    └──────────┘

SQL Standard VALUES Clause

From version 26.3, ClickHouse also supports the SQL standard VALUES clause as a table expression in FROM, as used in PostgreSQL, MySQL, DuckDB, and SQL Server. This syntax is rewritten internally to use the values table function described above.

Query

SELECT * FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS t(id, val);

Response

┌─id─┬─val─┐
│  1 │ a   │
│  2 │ b   │
│  3 │ c   │
└────┴─────┘

It can be used in CTEs:

Query

WITH cte AS (SELECT * FROM (VALUES (1, 'one'), (2, 'two')) AS t(id, name))
SELECT * FROM cte;

And in JOINs:

Query

SELECT t1.id, t1.val, t2.val2
FROM (VALUES (1, 'a'), (2, 'b')) AS t1(id, val)
JOIN (VALUES (1, 'x'), (2, 'y')) AS t2(id, val2) ON t1.id = t2.id;

Column aliases after AS t(col1, col2, ...) follow the standard SQL syntax for naming columns of derived tables. If omitted, columns are named c1, c2, etc.

​Syntax

​Arguments

​Returned value

​Examples

​SQL Standard VALUES Clause

​See also

Syntax

Arguments

Returned value

Examples

SQL Standard VALUES Clause

See also