In this post we are going to cover VALUES in Snowflake.
VALUES builds a table out of nothing but the values we type. We put it in the FROM clause, in brackets, with each row in brackets of its own. There is no CREATE TABLE, nothing is stored, and nothing is left behind in the account afterwards.
We will start with three rows and two columns, then give the thing a name and name its columns. After that we will use it as a lookup against a real table, use it to check a real column against what we expected to find in it, and break it twice on purpose so you can read the two rules. At the end we will reproduce a real bug in four lines.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 45 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
- Let's create some sample data
- Let's start with a table made of literals
- Can we treat it like a real table?
- Now let's use it as a lookup
- Now let's check what we expected against what is there
- Now let's break it: every row needs the same number of columns
- Now let's break it again: the types have to reconcile
- Now let's reproduce a bug in four lines
- And that closes Level 4
Let's create some sample data
We need almost nothing for this one, which is the point of the episode. The first few queries below create a table inside the query and then query it. They will run in a completely empty account.
Later on we join that invented table to a real one and then check a real column against it. For those we need ORDERS from this course's shared sample data, so here is the script that creates it.
-- Creates the ORDERS table used by the lookup and the data checks below.
-- Part of the shared sample-data script for this course; run it once.
-- VALUES itself needs none of this - every query on this page up to the lookup
-- runs with no table in the account at all.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
CREATE OR REPLACE TABLE ORDERS (
ORDER_ID INT,
CUSTOMER_ID INT,
ORDER_DATE DATE,
SHIP_DATE DATE,
STATUS VARCHAR,
AMOUNT NUMBER(10,2),
QUANTITY INT
);
INSERT INTO ORDERS VALUES
(1001, 101, '2023-01-12', '2023-01-15', 'SHIPPED', 4200.00, 12),
(1002, 102, '2023-01-28', '2023-02-02', 'SHIPPED', 890.50, 3),
(1003, 101, '2023-02-14', '2023-02-16', 'SHIPPED', 6150.00, 20),
(1004, 103, '2023-02-27', NULL, 'CANCELLED', 1500.00, 5),
(1005, 104, '2023-03-09', '2023-03-14', 'SHIPPED', 2310.75, 8),
(1006, 101, '2023-03-22', '2023-03-24', 'SHIPPED', 5020.00, 16),
(1007, 105, '2023-04-05', '2023-04-11', 'SHIPPED', 7890.00, 25),
(1008, 102, '2023-04-19', '2023-04-23', 'SHIPPED', 1120.00, 4),
(1009, 103, '2023-05-02', '2023-05-06', 'SHIPPED', 3340.25, 11),
(1010, 106, '2023-05-21', NULL, 'PENDING', 640.00, 2),
(1011, 101, '2023-06-08', '2023-06-10', 'SHIPPED', 8100.00, 27),
(1012, 104, '2023-06-25', '2023-06-30', 'SHIPPED', 1975.50, 7),
(1013, 105, '2023-07-11', '2023-07-15', 'SHIPPED', 6420.00, 21),
(1014, 102, '2023-07-29', '2023-08-03', 'RETURNED', 750.00, 3),
(1015, 107, '2023-08-14', '2023-08-17', 'SHIPPED', 9250.00, 30),
(1016, 103, '2023-08-30', '2023-09-04', 'SHIPPED', 2860.00, 9),
(1017, 101, '2023-09-16', '2023-09-19', 'SHIPPED', 5540.00, 18),
(1018, 106, '2023-10-03', '2023-10-08', 'SHIPPED', 1430.00, 5),
(1019, 105, '2023-10-22', '2023-10-26', 'SHIPPED', 7015.00, 23),
(1020, 107, '2023-11-07', '2023-11-10', 'SHIPPED', 10400.00, 34),
(1021, 102, '2023-11-24', '2023-11-29', 'SHIPPED', 980.00, 4),
(1022, 104, '2023-12-09', NULL, 'CANCELLED', 2250.00, 8),
(1023, 101, '2023-12-27', '2023-12-30', 'SHIPPED', 6730.00, 22),
(1024, 103, '2024-01-15', '2024-01-18', 'SHIPPED', 3910.00, 13),
(1025, 107, '2024-01-31', '2024-02-05', 'SHIPPED', 11200.00, 36),
(1026, 105, '2024-02-17', '2024-02-21', 'SHIPPED', 8340.00, 27),
(1027, 106, '2024-03-04', '2024-03-09', 'SHIPPED', 1690.00, 6),
(1028, 102, '2024-03-21', '2024-03-25', 'SHIPPED', 1240.00, 5),
(1029, 101, '2024-04-06', '2024-04-09', 'SHIPPED', 7460.00, 24),
(1030, 109, '2024-04-23', '2024-04-28', 'SHIPPED', 5180.00, 17),
(1031, 103, '2024-05-10', '2024-05-14', 'SHIPPED', 4025.00, 14),
(1032, 107, '2024-05-28', '2024-06-01', 'SHIPPED', 12850.00, 41),
(1033, 105, '2024-06-13', NULL, 'PENDING', 6900.00, 22),
(1034, 109, '2024-06-30', '2024-07-04', 'SHIPPED', 4760.00, 16),
(1035, 101, '2024-07-18', '2024-07-21', 'SHIPPED', 8820.00, 29),
(1036, 104, '2024-08-02', '2024-08-07', 'SHIPPED', 2540.00, 9),
(1037, 107, '2024-08-25', '2024-08-29', 'SHIPPED', 13100.00, 43),
(1038, 109, '2024-09-11', '2024-09-15', 'SHIPPED', 5930.00, 19),
(1039, 102, '2024-09-29', '2024-10-03', 'RETURNED', 1080.00, 4),
(1040, 101, '2024-10-16', '2024-10-19', 'SHIPPED', 9640.00, 31);
-- One deliberate NULL amount, inserted separately so it is easy to see why it
-- is there. It makes COUNT(*), COUNT(AMOUNT) and SUM() give different answers.
INSERT INTO ORDERS VALUES
(1041, 106, '2024-11-05', NULL, 'PENDING', NULL, 3);That script inserts 41 rows, the last of them on its own at the end. Hold on to the 41, because a grid further down this page has to add up to it.
Let's start with a table made of literals
Here is the whole idea. We put VALUES in the FROM clause, in brackets, with each row in brackets of its own and commas between them.
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three'));| COLUMN1 | COLUMN2 |
|---|---|
| 1 | one |
| 2 | two |
| 3 | three |
Three rows and two columns, out of a table that does not exist anywhere in our account. There is nothing to create and nothing to clean up afterwards.
Now have a look at the column headings. COLUMN1 and COLUMN2. The literals we typed carried no names, so there was nothing for Snowflake to take a name from, and it invented two.
Now let's name the thing, and name its columns
We give the whole bracket a table alias, then list column names in brackets after that alias.
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS T (N, WORD)
ORDER BY N;| N | WORD |
|---|---|
| 1 | one |
| 2 | two |
| 3 | three |
Same three rows, and the headings now read N and WORD. The names come from the alias, not from the data. That is a different rule from the one we are used to, where a column's name comes from the table it lives in.
Writing the alias is a habit worth forming immediately. Without it you cannot write a WHERE clause, an ORDER BY or a JOIN condition against those columns without referring to them as COLUMN1 and COLUMN2, and a query written that way is unreadable a week later.
Can we treat it like a real table?
We can. It is not a special construct that only works in simple queries. Anything we can point at a table, we can point at this. Let's try aggregates.
SELECT COUNT(*) AS ROWS_IN_IT, MIN(N) AS SMALLEST, MAX(N) AS LARGEST
FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS T (N, WORD);| ROWS_IN_IT | SMALLEST | LARGEST |
|---|---|---|
| 3 | 1 | 3 |
COUNT(*), MIN and MAX ran over the literals exactly as they would over stored rows, and collapsed them to one row in the usual way.
The same is true of JOIN, GROUP BY, DISTINCT, set operators and subqueries. We use all of those further down this page.
Now let's use it as a lookup
This is what VALUES actually earns its place doing. ORDERS has a STATUS column holding short codes. We want a friendlier label, plus a flag for whether that status counts as revenue.
We could create a four-row table and maintain it. Or we could write those four rows where they are used, alias them and join.
SELECT O.ORDER_ID, O.STATUS, M.LABEL, M.IS_REVENUE
FROM ORDERS O
JOIN (VALUES ('SHIPPED', 'Shipped', TRUE),
('PENDING', 'Pending', FALSE),
('CANCELLED', 'Cancelled', FALSE),
('RETURNED', 'Returned', FALSE)) AS M (CODE, LABEL, IS_REVENUE)
ON M.CODE = O.STATUS
ORDER BY O.ORDER_ID
LIMIT 12;| ORDER_ID | STATUS | LABEL | IS_REVENUE |
|---|---|---|---|
| 1001 | SHIPPED | Shipped | TRUE |
| 1002 | SHIPPED | Shipped | TRUE |
| 1003 | SHIPPED | Shipped | TRUE |
| 1004 | CANCELLED | Cancelled | FALSE |
| 1005 | SHIPPED | Shipped | TRUE |
| 1006 | SHIPPED | Shipped | TRUE |
| 1007 | SHIPPED | Shipped | TRUE |
| 1008 | SHIPPED | Shipped | TRUE |
| 1009 | SHIPPED | Shipped | TRUE |
| 1010 | PENDING | Pending | FALSE |
| 1011 | SHIPPED | Shipped | TRUE |
| 1012 | SHIPPED | Shipped | TRUE |
Every order now has a label and a revenue flag from a table that does not exist. The grid shows 12 rows because the query asks for 12. The table underneath it holds 41.
Now let's group by it
The invented table behaves like any other on the GROUP BY side too. Let's group by its label rather than the raw code.
SELECT M.LABEL, COUNT(*) AS N_ORDERS, SUM(O.AMOUNT) AS TOTAL
FROM ORDERS O
JOIN (VALUES ('SHIPPED', 'Shipped', TRUE),
('PENDING', 'Pending', FALSE),
('CANCELLED', 'Cancelled', FALSE),
('RETURNED', 'Returned', FALSE)) AS M (CODE, LABEL, IS_REVENUE)
ON M.CODE = O.STATUS
GROUP BY M.LABEL
ORDER BY TOTAL DESC NULLS LAST;| LABEL | N_ORDERS | TOTAL |
|---|---|---|
| Shipped | 34 | 192307.00 |
| Pending | 3 | 7540.00 |
| Cancelled | 2 | 3750.00 |
| Returned | 2 | 1830.00 |
Four rows, one per label in the map. Now add the order counts in that grid together. They come to 41, which is every row the setup script inserted.
That total is doing real work. This is an inner join, so an order whose status was missing from the map would have vanished silently from both of the last two grids. The counts reaching 41 is the evidence that the map covers the data. We do not have to take the map's word for itself.
Now let's check what we expected against what is there
Here is the use that is less obvious and, once you have it, hard to stop using. We write down the values we expect a column to hold, then subtract one set from the other with MINUS, which was Episode 35.
First direction. What is in the data that is not in our list?
SELECT DISTINCT STATUS FROM ORDERS
MINUS
SELECT CODE FROM (VALUES ('SHIPPED'), ('PENDING'), ('CANCELLED'), ('RETURNED')) AS M (CODE);| STATUS |
|---|
Nothing. An empty grid is the answer here, not a failure. It says there is no status in ORDERS that our list did not anticipate.
Now let's turn it round, and add one extra code to the list that nobody has used.
SELECT CODE FROM (VALUES ('SHIPPED'), ('PENDING'), ('CANCELLED'), ('RETURNED'),
('REFUNDED')) AS M (CODE)
MINUS
SELECT DISTINCT STATUS FROM ORDERS;| CODE |
|---|
| REFUNDED |
One row comes back. It is the code that exists in our expectation and nowhere in the data. That is a different question with a different kind of answer. Not bad data, but a value we planned for that never occurs.
The two directions answer two things people routinely confuse. Data MINUS list finds values you are not handling, which is usually a bug. List MINUS data finds branches of your logic that never run, which is usually dead code or a stale assumption. Both are four lines of SQL.
Now let's break it: every row needs the same number of columns
There are two rules about what we may put in a VALUES list, and both are easier to show as errors than to describe.
The first is the obvious one. Let's give one row fewer values than the others and see what Snowflake says.
SELECT * FROM (VALUES (1, 'one'), (2), (3, 'three')) AS T (N, WORD);002012 (22000): SQL compilation error: Inconsistent number of values in the VALUES clause expecting 2 but got 1
The middle row has one value where the other two have two. The error counts them for us and says which number it expected. A ragged list is a compilation error, not a row quietly padded with NULLs.
Now let's break it again: the types have to reconcile
The second rule is the one that catches people, because the intuitive guess about what Snowflake will do is wrong.
Let's put words in the second column of two rows and a number in the second column of the third.
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 3)) AS T (N, WORD);100038 (22018): Numeric value 'one' is not recognized
The reasonable expectation is that a mixed column becomes text, because text can hold anything. That is not what happens.
One row in that column is a number, so Snowflake decides the column is numeric. Then it has to turn a word into a number, which is the error we are reading.
We can see what it decides for a list that does reconcile by asking it.
SELECT TYPEOF(TO_VARIANT(N)) AS N_TYPE,
TYPEOF(TO_VARIANT(WORD)) AS WORD_TYPE
FROM (VALUES (1, 'one')) AS T (N, WORD);| N_TYPE | WORD_TYPE |
|---|---|
| INTEGER | VARCHAR |
One type per column, chosen once for the whole list, and every row has to fit it. The column of integers is an integer column. The column of words is a VARCHAR.
The failure is not symmetrical, which is why it surprises people. The type is settled by the whole list, so adding one numeric row to a column of words breaks every other row in that column — not the row you added. If a hand-written VALUES list suddenly stops compiling after an edit, look at the column you touched, not the row.
What about NULLs?
A NULL has no type to disagree with, so it fits any column. Let's put one in each.
SELECT * FROM (VALUES (1, 'one'), (2, NULL), (NULL, 'three')) AS T (N, WORD)
ORDER BY N NULLS LAST;| N | WORD |
|---|---|
| 1 | one |
| 2 | NULL |
| NULL | three |
Three rows. There is a NULL in the number column and a NULL in the word column, and Snowflake accepted both. A NULL in a VALUES list behaves like a NULL anywhere else, including in the places we would rather it did not. That is the next thing we will try.
Now let's reproduce a bug in four lines
This is the argument for knowing VALUES exists at all. Episode 38 showed the NOT IN trap: a NOT IN list containing a single NULL returns nothing at all, with no error and no warning. Demonstrating it there took two tables and a setup script.
Let's do the same trap with no tables.
SELECT N FROM (VALUES (1), (2), (3)) AS T (N)
WHERE N NOT IN (SELECT V FROM (VALUES (1), (NULL)) AS S (V));| N |
|---|
Empty. Every row on the left disappeared, including the ones that are plainly not in the list on the right.
Now let's take the NULL out of the right-hand list and change nothing else.
SELECT N FROM (VALUES (1), (2), (3)) AS T (N)
WHERE N NOT IN (SELECT V FROM (VALUES (1)) AS S (V))
ORDER BY N;| N |
|---|
| 2 |
| 3 |
Two rows come back, 2 and 3. The 1 stays out, and that is correct, because 1 really is in the list on the right. Same operator, same left-hand list, one value removed from the right, and that is the difference between an empty result and a right one.
That is the real reason to learn this construct. We can test a claim about SQL in the time it takes to type it, with no table, no cleanup and nothing left in the account to go stale. It is the right tool for a code review, a bug report or an argument about behaviour.
And that closes Level 4
This level was about queries built out of other queries. Scalar subqueries, the NOT IN NULL trap, EXISTS and NOT EXISTS, the quantified comparisons ANY, ALL and SOME, correlated subqueries, derived tables, common table expressions and recursion. And now a table with no query behind it at all.
Level 5 is window functions, which is where a lot of people decide SQL is more interesting than they thought. It starts with one word.
Key points
VALUESin aFROMclause builds a table out of literals. NoCREATE TABLE, nothing left in the account afterwards.- Without an alias the columns are called
COLUMN1andCOLUMN2. The names come from the alias, not from the data —AS T (N, WORD). - It behaves like a table everywhere:
JOIN,GROUP BY, aggregates, set operators and subqueries all work against it. - A small lookup joined from a
VALUESlist lives next to the query that uses it, so it cannot drift out of step with that query. - Because the lookup join is an inner join, checking the grouped counts against the table's own row count tells you whether the map covers every value in the data.
MINUSagainst aVALUESlist is a data check in both directions: data minus list finds values you do not handle, list minus data finds logic that never runs.- Every row must have the same number of columns, or you get a compilation error rather than padded
NULLs. - One type is chosen per column for the whole list. A single numeric row makes the column numeric and breaks the text rows — it does not coerce everything to text.
NULLis accepted in any column, and behaves exactly as it does anywhere else, traps included.
Episode 46 — OVER(), and the start of Level 5. An ordinary aggregate collapses your rows into one. Add two brackets to the same function and every row stays, with the answer attached to each one — plus the two errors you get for trying to filter on it in WHERE.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including both errors and both empty results. The ORDERS data is invented demo data created by the setup script above. No performance or benchmark claims are made anywhere in this series. Clause behaviour follows the Snowflake VALUES documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.