VALUES clause

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Produces an inline temporary table for use within the query.

Syntax

VALUES {expression | ( expression [, ...] ) } [, ...] [table_alias]

SELECT expression [, ...] [table_alias]

Parameters

  • expression

    A combination of one or more values, operators and SQL functions that results in a value.

  • table_alias

    An optional label to allow the result set to be referenced by name.

Each tuple constitutes a row.

If there is more than one row the number of fields in each tuple must match.

When using the VALUES syntax, if no tuples are specified, each expression equates to a single field tuple.

When using the SELECT syntax all expressions constitute a single row temporary table.

The nth field of each tuple must share a least common type. If table_alias specifies column names, their number must match the number of expressions per tuple.

The result is a temporary table where each column’s type is the least common type of the matching tuples fields.

Examples

-- single row, without a table alias
> VALUES ("one", 1);
  one    1

-- Multiple rows, one column
> VALUES 1, 2, 3;
 1
 2
 3

-- three rows with a table alias
> SELECT data.a, b
    FROM VALUES ('one', 1),
                ('two', 2),
                ('three', NULL) AS data(a, b);
   one    1
   two    2
 three NULL

-- complex types with a table alias
> SELECT a, b
  FROM VALUES ('one', array(0, 1)),
              ('two', array(2, 3)) AS data(a, b);
 one [0, 1]
 two [2, 3]

-- Using the SELECT syntax
> SELECT 'one', 2
 one 2