Values
Let's share core values sometime
Description
Produces an inline temporary table for use within the query. (Databricks SQL Language Reference VALUES)
In the SELECT statement, the VALUES sub-clause of the FROM clause allows the specification of a set of constants to be used to form a finite set of rows. (Snowflake SQL Language Reference VALUES)
Syntax
VALUES {expression | ( expression [, ...] ) } [, ...] [table_alias]
SELECT expression [, ...] [table_alias]
Sample Source Patterns
Setup data
Databricks
CREATE TEMPORARY VIEW number1(c) AS VALUES (3), (1), (2), (2), (3), (4);
Snowflake
CREATE TEMPORARY TABLE number1(c int);
INSERT INTO number1 VALUES (3), (1), (2), (2), (3), (4);
Pattern code
Databricks
-- 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
Snowflake
-- single row, without a table alias
SELECT * FROM (VALUES ('one', 1));
-- Multiple rows, one column
SELECT * FROM (VALUES (1), (2), (3));
-- three rows with a table alias
SELECT a, b
FROM (VALUES ('one', 1),
('two', 2),
('three', NULL)) AS data(a, b);
-- complex types with a table alias
SELECT a, b
FROM
(VALUES ('one', '[0, 1]'),
('two', '[2, 3]')
) AS data(a, b);
-- Using the SELECT syntax
SELECT 'one', 2
Known Issues
No issues were found
Related EWIs
No related EWIs
Last updated