In this post we are going to cover UNION and UNION ALL in Snowflake.
These two are set operators. A join puts two tables side by side and gives us wider rows. A set operator does something else. It takes two complete queries and stacks one result on top of the other. There is no ON clause and no matching of any kind.
We will start by stacking two results. Then we will see what that one extra word ALL is doing. And then we will watch UNION quietly throw away a row we wanted, with no error and no warning.
Every query below was run on a real Snowflake account, and every result you see is the output it gave back.
Episode 34 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.
Let's create some sample data
Every query on this page runs against two small tables. The script below creates both of them. The first INSERT lists eight employees and the second lists four departments.
Copy this into a Snowflake worksheet and run it once. Then every grid on this page is yours to reproduce.
-- Sample data for this post.
-- EMPLOYEES was created at the start of this course and DEPARTMENTS at the
-- start of Level 3. Both are repeated here so this page runs on its own.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
CREATE OR REPLACE TABLE EMPLOYEES (
EMP_ID INT,
FIRST_NAME VARCHAR,
LAST_NAME VARCHAR,
EMAIL VARCHAR,
DEPT_ID INT,
MANAGER_ID INT,
SALARY NUMBER(10,2),
COMMISSION_PCT NUMBER(4,2),
HIRE_DATE DATE
);
INSERT INTO EMPLOYEES VALUES
(1, 'Aisha', 'Khan', 'aisha.khan@example.com', 10, 6, 118000.00, NULL, '2019-03-11'),
(2, 'Marcus', 'Reid', 'marcus.reid@example.com', 10, 6, 96500.00, NULL, '2021-07-01'),
(3, 'Lena', 'Fischer', 'lena.fischer@example.com', 20, 6, 104000.00, 0.05, '2018-01-22'),
(4, 'Tomas', 'Novak', 'tomas.novak@example.com', 20, 3, 87250.00, NULL, '2022-09-05'),
(5, 'Priya', 'Raman', 'priya.raman@example.com', 30, 6, 96500.00, 0.10, '2020-11-17'),
(6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com', 10, NULL, 131000.00, NULL, '2016-05-30'),
(7, 'Sofia', 'Marino', 'sofia.marino@example.com', 30, 5, 71400.00, 0.08, '2023-02-13'),
(8, 'Grace', 'Lin', 'grace.lin@example.com', NULL, 6, 92000.00, NULL, '2021-04-19');
CREATE OR REPLACE TABLE DEPARTMENTS (
DEPT_ID INT,
DEPT_NAME VARCHAR,
LOCATION VARCHAR,
BUDGET NUMBER(12,2)
);
INSERT INTO DEPARTMENTS VALUES
(10, 'Engineering', 'Charlotte', 2400000.00),
(20, 'Finance', 'Charlotte', 1100000.00),
(30, 'Marketing', 'Raleigh', 750000.00),
(40, 'Operations', 'Raleigh', 640000.00);This episode only uses three columns of EMPLOYEES. They are FIRST_NAME, DEPT_ID and SALARY. From DEPARTMENTS we only need DEPT_ID and DEPT_NAME.
This episode captured no SELECT * of either table, so you will not find a raw grid of EMPLOYEES or DEPARTMENTS below and no claim is made about one. Every count quoted on this page is read off a grid printed beside it.
Two rows in that script matter more than the rest, and they are easy to read straight past. Marcus and Priya have the same salary. Marcus is in department 10. Priya is in department 30. They are two different people. Keep them in mind, because we come back to them later.
Let's start with UNION ALL
Let's write two queries, one for each of two departments, and put UNION ALL between them.
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID = 10
UNION ALL
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID = 20
ORDER BY DEPT_ID, FIRST_NAME;| FIRST_NAME | DEPT_ID |
|---|---|
| Aisha | 10 |
| Daniel | 10 |
| Marcus | 10 |
| Lena | 20 |
| Tomas | 20 |
We got back five rows. Aisha, Daniel and Marcus from department 10, then Lena and Tomas from department 20.
Nothing was matched. Nothing was filtered. Nothing was calculated. One result was simply put underneath the other.
There are rules, but they are about the shape of the two branches and not about what is in them. Each branch has to return the same number of columns. And the types in each position have to be close enough that Snowflake can settle on one type for the output column. Nothing on this page tests the edges of that second rule.
The branches do not have to come from the same table, or from any table at all. Further down this page we stack EMPLOYEES and DEPARTMENTS, which have no key in common. The operator cares about column count and type, and about nothing else.
Now let's see what the word ALL does
Let's take one query, stack it on top of itself, and count what comes back.
SELECT COUNT(*) AS N FROM (
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES
UNION ALL
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES
);| N |
|---|
| 16 |
Sixteen. Every row of EMPLOYEES is in there twice, because that is exactly what we asked for.
Now let's remove one word. Same two branches, same table, same columns.
SELECT COUNT(*) AS N FROM (
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES
UNION
SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES
);| N |
|---|
| 8 |
Eight. Half as many rows, and the only thing we changed was the word ALL.
To be sure nothing else moved between those two runs, let's ask for both counts in one query.
SELECT
(SELECT COUNT(*) FROM (SELECT FIRST_NAME FROM EMPLOYEES UNION ALL SELECT FIRST_NAME FROM EMPLOYEES)) AS WITH_ALL,
(SELECT COUNT(*) FROM (SELECT FIRST_NAME FROM EMPLOYEES UNION SELECT FIRST_NAME FROM EMPLOYEES)) AS WITHOUT_ALL;| WITH_ALL | WITHOUT_ALL |
|---|---|
| 16 | 8 |
So here is the whole difference. UNION is UNION ALL plus a deduplication pass. It stacks the branches, and then it removes any row that is identical to a row already in the result.
It would be easy to turn that extra pass into a claim about speed. Nothing in this series is timed, so no such claim is made here. The argument for UNION ALL on this page is not that it is faster. It is that UNION can change your answer, which is what we look at next.
Can UNION cost us a row we wanted?
Stacking a table on top of itself makes duplicates on purpose, so removing them looks harmless. Let's look at a case where it is not.
Let's take departments 10 and 30 and ask for everybody in them, with their names, stacked with UNION ALL and sorted by salary.
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE DEPT_ID = 10
UNION ALL
SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE DEPT_ID = 30
ORDER BY SALARY DESC;| FIRST_NAME | SALARY |
|---|---|
| Daniel | 131000.00 |
| Aisha | 118000.00 |
| Marcus | 96500.00 |
| Priya | 96500.00 |
| Sofia | 71400.00 |
Five people and five rows, and all five rows are different from each other. Now find Marcus and Priya in that grid. They both earn 96500.00. Different first names, different departments, two different employees, and the same number in the SALARY column.
There is one more thing in that query worth pointing out. There is a single ORDER BY, it is written once, and it sits at the very end. It has sorted the whole result from 131000.00 down to 71400.00, straight across both branches. An ORDER BY belongs to the combined result, not to a branch.
Now let's ask for the salary and nothing else
Same two departments and the same five salaries. This time FIRST_NAME is not in the select list, and the second branch is a query that returns nothing at all.
SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID IN (10, 30)
UNION ALL
SELECT SALARY FROM EMPLOYEES WHERE 1 = 0
ORDER BY SALARY;| SALARY |
|---|
| 71400.00 |
| 96500.00 |
| 96500.00 |
| 118000.00 |
| 131000.00 |
Five rows, and 96500.00 is in there twice. Once for Marcus and once for Priya. Both people are still represented.
The second branch of that query is SELECT SALARY FROM EMPLOYEES WHERE 1 = 0, which returns nothing. That is deliberate, and it makes the next step stronger rather than weaker. All five rows come from the first branch, so whatever happens next is not something happening at the seam between two results. UNION deduplicates the whole combined result.
Now let's drop the word ALL, and change nothing else.
SELECT SALARY FROM EMPLOYEES WHERE DEPT_ID IN (10, 30)
UNION
SELECT SALARY FROM EMPLOYEES WHERE 1 = 0
ORDER BY SALARY;| SALARY |
|---|
| 71400.00 |
| 96500.00 |
| 118000.00 |
| 131000.00 |
Four rows. 71400.00, 96500.00, 118000.00 and 131000.00. 96500.00 now appears once.
One of the two 96500.00 rows is gone. It was not a duplicated record. It was not a data quality problem. It was not a row somebody entered twice. It was a second real employee, whose only visible attribute happened to match somebody else's.
And notice what we cannot say about it. Which row went, Marcus's or Priya's, is not a question the result can answer. Two rows reading 96500.00 went in and one came out. There is nothing in the output to tell them apart, which is exactly why UNION felt entitled to discard one of them.
UNION does not remove duplicate records. It removes duplicate rows of your result. And what counts as a duplicate is decided entirely by the columns you put in the SELECT list. Look back at the grid with FIRST_NAME in it. With that column present the two rows are plainly not identical, so there is nothing there for a deduplication pass to collapse.
| Select list | Are Marcus's and Priya's rows identical? | What the deduplication pass has to work on |
|---|---|---|
SALARY alone | Yes. Both read 96500.00, as the UNION ALL grid shows | A pair it is entitled to collapse, which is why the UNION grid came back one row short |
FIRST_NAME, SALARY | No. The names differ, as the five-row grid shows | Nothing. There is no identical pair left to remove |
That is the trap in full. The dangerous query is not the one with obviously duplicated data in it. It is the narrow SELECT list. One column, or two, or a SELECT DISTINCT-shaped habit. Two unrelated rows collapse into one, and the row count is the only evidence it ever happened.
Now let's look at the column names
The names come from the first branch
Each branch has its own select list and can alias its columns however it likes. Let's give the two branches different names for the same output column and see which one we get.
SELECT FIRST_NAME AS NAME, DEPT_ID FROM EMPLOYEES WHERE EMP_ID = 1
UNION ALL
SELECT DEPT_NAME AS SOMETHING_ELSE, DEPT_ID FROM DEPARTMENTS WHERE DEPT_ID = 40;| NAME | DEPT_ID |
|---|---|
| Aisha | 10 |
| Operations | 40 |
The second branch aliased its first column as SOMETHING_ELSE. The output heading reads NAME, which came from branch one, and the value from branch two is sitting underneath it.
Positions are matched up and names are not. Which also means a set operator will happily stack two columns that have nothing to do with each other, as long as their types line up.
The branches must return the same number of columns
This one Snowflake will not let past. Let's put one column against two and read what it says.
SELECT FIRST_NAME FROM EMPLOYEES
UNION ALL
SELECT DEPT_NAME, DEPT_ID FROM DEPARTMENTS;001789 (42601): SQL compilation error: invalid number of result columns for set operator input branches, expected 1, got 2 in branch 2
invalid number of result columns for set operator input branches. The message gives us the count it expected, the count it got, and which branch it found the problem in. On a set operator with several branches that last detail saves real time.
Compare the two ways this page has failed. A mismatched column count is a compilation error. It is loud, it is immediate, and it is impossible to ignore. A missing ALL is silent, it succeeds, and it returns a plausible result with a row missing. The one that cannot hurt you is the one that shouts.
Let's make one list out of two unrelated tables
Here is what people actually reach for UNION ALL to do. EMPLOYEES and DEPARTMENTS share no key and describe different things, so no join makes sense between them.
But both of them have something we can call a name. So both can contribute rows to one list, and we add a literal column to say which is which.
SELECT 'EMPLOYEE' AS KIND, FIRST_NAME AS NAME FROM EMPLOYEES
UNION ALL
SELECT 'DEPARTMENT', DEPT_NAME FROM DEPARTMENTS
ORDER BY KIND, NAME;| KIND | NAME |
|---|---|
| DEPARTMENT | Engineering |
| DEPARTMENT | Finance |
| DEPARTMENT | Marketing |
| DEPARTMENT | Operations |
| EMPLOYEE | Aisha |
| EMPLOYEE | Daniel |
| EMPLOYEE | Grace |
| EMPLOYEE | Lena |
| EMPLOYEE | Marcus |
| EMPLOYEE | Priya |
| EMPLOYEE | Sofia |
| EMPLOYEE | Tomas |
Twelve rows. Four departments and eight employees, each one labelled by the KIND column. That literal string is doing real work. Without it we would have a list of names with no way to tell what kind of thing each one is.
This is also a case where the choice of operator needs no thought. A department name and an employee's first name cannot collide in a way that means anything, and the KIND column keeps the two halves apart even if a value did repeat. UNION ALL is correct here, and UNION would only add a risk in exchange for nothing.
So which one do we reach for?
The habit worth building is short. Default to UNION ALL, and write UNION only on purpose.
| Situation | Use | Why |
|---|---|---|
| Stacking results that cannot overlap, like different tables, different periods, or a labelled list | UNION ALL | There is nothing to deduplicate, so a deduplication pass can only cost you rows you wanted |
| Stacking results that do overlap, and you want each distinct row once | UNION | This is the job it exists for. Check your SELECT list first |
| You are not sure whether the branches overlap | UNION ALL, then count | An unexpected duplicate is visible and fixable. A silently removed row is neither |
Before you write UNION, ask one question. Could two different things in my data produce an identical row, given the columns I selected?
If the answer is yes, or if you cannot tell, then either use UNION ALL or widen the select list until the rows that should be distinct actually are. A key column is usually enough.
Key points
- A set operator stacks one result on top of another. No
ONclause, no matching and no condition. The second result is appended to the first. - Both branches must return the same number of columns, with compatible types in each position. Snowflake names the offending branch when they do not.
UNION ALLkeeps every row.UNIONisUNION ALLplus a deduplication pass over the whole combined result.- The same query stacked on itself gives us 16 rows with
ALLand 8 without. One captured result on this page shows both counts at once. UNIONremoves duplicate rows of the result, not duplicate records. Two different employees who share a salary are one row onceSALARYis the only column we selected.- Which of two identical rows gets discarded is not something the result can tell us. That is the whole problem, not a detail of it.
- Column names come from the first branch. The other branches' aliases are ignored, and only column position matters.
ORDER BYbelongs to the combined result. Write it once, at the end.- Default to
UNION ALL. Reach forUNIONonly when you want the deduplication and you know what a duplicate means in yourSELECTlist.
In the next post we will look at INTERSECT, MINUS and EXCEPT. UNION stacks two results. These three compare them. What is in both, what is in one and not the other, and why swapping the two branches of a MINUS asks a different question instead of reversing the answer.
Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the sample script above. The EMPLOYEES and DEPARTMENTS data is invented demo data created by that script. The error pane is Snowflake's own message, captured as returned and not paraphrased. No performance claims are made anywhere in this series. Nothing here was timed, so nothing about UNION being slower than UNION ALL is said. The case made above is that it can return a different answer. Operator behaviour is described from the Snowflake set operator documentation.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.