VALUES clause
Applies to: 
Databricks SQL Databricks Runtime
Produces an inline temporary table for use within the query.
Syntax
VALUES {expression | ( expression [, ...] ) } [, ...] [table_alias]
SELECT expression [, ...] [table_alias]
Parameters
-
A combination of one or more values, operators and SQL functions that results in a value.
-
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