Snowflake SQL Tutorial 35 - INTERSECT, MINUS and EXCEPT in Snowflake

Snowflake SQL · Episode 35

In this post we are going to cover INTERSECT, MINUS and EXCEPT in Snowflake.

These are set operators, the same family as UNION. But they do a different job. UNION stacks two results on top of each other. These three compare two results. INTERSECT gives us what is in both of them. MINUS gives us what is in the first one and not in the second. EXCEPT is another name for MINUS.

We will set up two lists first, so that every answer is one we can predict before we read it. Then we will take the three operators one at a time, we will see why swapping the two branches of a MINUS asks a different question, and we will finish by writing a reconciliation in one query.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 35 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's look at the two lists before we compare them
3. Let's start with INTERSECT
4. Now let's try MINUS
5. Can we just swap the two branches around?
6. Now let's select two columns instead of one
7. Now let's write a reconciliation in one query
8. So which one do we reach for?

Let's create some sample data

Everything 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.

Sample data

-- 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);

Every query below compares one column, DEPT_ID, across those two tables. So that is the only column we need to keep an eye on.

What is not on this page

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. Where this page talks about individual people, like who is in department 10 or whose DEPT_ID is NULL, the evidence is the INSERT statement above, which you can read for yourself.

Let's look at the two lists before we compare them

Comparing two results is only useful if we can see both of them first. So let's put both lists on screen before a single operator appears.

First, the distinct departments that employees are actually in.

Query — the DEPT_IDs in EMPLOYEES

SELECT DISTINCT DEPT_ID FROM EMPLOYEES ORDER BY DEPT_ID NULLS LAST;

Result4 rows · 1 column

DEPT_ID
10
20
30
NULL

And now the departments that exist.

Query — the DEPT_IDs in DEPARTMENTS

SELECT DEPT_ID FROM DEPARTMENTS ORDER BY DEPT_ID;

Result4 rows · 1 column

DEPT_ID
10
20
30
40

The two lists agree on three values and they differ at both ends. EMPLOYEES has a NULL that DEPARTMENTS does not have. DEPARTMENTS has 40 that EMPLOYEES does not have.

That shape is on purpose, and it is what makes every result on this page checkable. Each operator below returns something we can work out from these two grids before we read the answer.

Why only one of those queries says DISTINCT

DEPARTMENTS.DEPT_ID is that table's key, so SELECT DEPT_ID FROM DEPARTMENTS needs no DISTINCT. The employees query needs it: department 10 appears three times in the sample data above, once each for Aisha, Marcus and Daniel.

Let's start with INTERSECT

INTERSECT goes between two complete queries, the same way UNION does. It gives us back the rows that appear in both branches.

Query

SELECT DEPT_ID FROM EMPLOYEES
INTERSECT
SELECT DEPT_ID FROM DEPARTMENTS
ORDER BY DEPT_ID;

Result3 rows · 1 column

DEPT_ID
10
20
30

10, 20 and 30. Read that against the two grids in the previous section and both of the missing values make sense. 40 is in DEPARTMENTS, but no employee is in it. The NULL is in EMPLOYEES, but there is nothing on the departments side for it to pair with.

Now let's check whether it removes duplicates

Department 10 has three employees. Aisha, Marcus and Daniel, as we saw in the sample data. So the left-hand branch of that query, which was written without DISTINCT, offers 10 three times. The result above shows it once.

Rather than take a row count off a grid you might be skimming, let's count it directly.

Query — the same INTERSECT, counted

SELECT COUNT(*) AS ROWS_BACK FROM (
  SELECT DEPT_ID FROM EMPLOYEES
  INTERSECT
  SELECT DEPT_ID FROM DEPARTMENTS
);

Result1 row · 1 column

ROWS_BACK
3

INTERSECT returns distinct rows, the same way UNION does. And unlike UNION there is no ALL variant of it to reach for. There is none for MINUS either. So the deduplication is not something we opt into or out of.

Same trap as the last episode

Which means the lesson from Episode 34 comes straight across. These operators compare whole rows of your result, and a narrow SELECT list can make two unrelated things look identical. An INTERSECT over one column tells you that a value is present on both sides. It does not tell you how many rows on either side carried it.

NULL behaves differently here than in a join

One difference from Level 3 worth knowing before you read the documentation. A join's ON clause tests equality, and NULL is never equal to anything, so NULL keys never match. A set operator compares rows the way DISTINCT does, where two NULLs in the same position count as the same row. Nothing captured on this page exercises that, because only one of our two lists has a NULL in it at all. So treat it as context for reading the docs, not as something these grids prove.

Now let's try MINUS

MINUS gives us the rows of the first branch that do not appear in the second branch.

So let's put DEPARTMENTS first and EMPLOYEES second. The question we are asking is “which departments has nobody in them?”

Query

SELECT DEPT_ID FROM DEPARTMENTS
MINUS
SELECT DEPT_ID FROM EMPLOYEES
ORDER BY DEPT_ID;

Result1 row · 1 column

DEPT_ID
40

40. That is Operations, with no employees assigned to it. It is also the same answer Episode 25 arrived at with an anti-join, written here as a comparison of two lists instead of a join plus an IS NULL test.

Is EXCEPT the same thing?

Rather than tell you, let's run the identical query with the one word changed.

Query

SELECT DEPT_ID FROM DEPARTMENTS
EXCEPT
SELECT DEPT_ID FROM EMPLOYEES
ORDER BY DEPT_ID;

Result1 row · 1 column

DEPT_ID
40

Identical result. MINUS and EXCEPT are two spellings of one operator. EXCEPT is the ANSI SQL keyword and MINUS is the one Oracle developers arrive with. Use whichever your team reads more easily, and do not go looking for a difference in behaviour. Those two grids are the same grid.

Can we just swap the two branches around?

This is the part that catches people. Let's put EMPLOYEES first and DEPARTMENTS second, and see what comes back.

Query

SELECT DEPT_ID FROM EMPLOYEES
MINUS
SELECT DEPT_ID FROM DEPARTMENTS
ORDER BY DEPT_ID NULLS LAST;

Result1 row · 1 column

DEPT_ID
NULL

Not 40. We got NULL. That is Grace's DEPT_ID. She is the one employee in the sample data who is not assigned to a department, so her value is in the left-hand list and not in the right-hand one.

Swapping the branches of a MINUS does not reverse the answer. It asks a different question. The first branch supplies the candidates and the second branch only removes. So which table we put on the left decides what we are looking for.

Query	Returns	The question it is actually asking
DEPARTMENTS MINUS EMPLOYEES	40	Which departments have nobody in them?
EMPLOYEES MINUS DEPARTMENTS	NULL	Which DEPT_IDs do employees carry that are not real departments?

Only one of these three cares about order

INTERSECT has no such problem, because “in both” does not care which side we wrote first. MINUS and EXCEPT are the only set operators here where branch order changes the meaning. That is reason enough to read a MINUS out loud before you trust it.

Now let's select two columns instead of one

There is one more property, and it only becomes visible once we select more than one column. A set operator compares the whole row, not the first column of it.

Let's take the people in department 10 and intersect them with everybody earning more than 100000, and this time select two columns.

Query

SELECT DEPT_ID, SALARY FROM EMPLOYEES WHERE DEPT_ID = 10
INTERSECT
SELECT DEPT_ID, SALARY FROM EMPLOYEES WHERE SALARY > 100000
ORDER BY SALARY;

Result2 rows · 2 columns

DEPT_ID	SALARY
10	118000.00
10	131000.00

Two rows, 118000.00 and 131000.00, both in department 10. Those are Aisha and Daniel, who satisfy both branches.

Marcus is the interesting absence. He is in department 10, so the left branch produces a row for him. But he earns 96500.00, so the right branch does not. His row exists on one side and not the other, so the pair does not match and he is not in the answer. The match has to hold on every column we selected, together.

Column count cuts both ways

That is the same mechanism as the deduplication warning further up this page, seen from the other direction. Widening the SELECT list makes rows less likely to match. That protects us from collapsing two different things into one. It also means an INTERSECT or a MINUS over several columns is answering a narrower question than we might have had in mind.

Now let's write a reconciliation in one query

This is what people actually reach for MINUS to do. We have both directions available now, so let's run both, label each one, and stack them with UNION ALL.

Query

SELECT 'IN EMPLOYEES ONLY' AS SIDE, DEPT_ID FROM (
  SELECT DEPT_ID FROM EMPLOYEES MINUS SELECT DEPT_ID FROM DEPARTMENTS
)
UNION ALL
SELECT 'IN DEPARTMENTS ONLY', DEPT_ID FROM (
  SELECT DEPT_ID FROM DEPARTMENTS MINUS SELECT DEPT_ID FROM EMPLOYEES
)
ORDER BY SIDE, DEPT_ID NULLS LAST;

Result2 rows · 2 columns

SIDE	DEPT_ID
IN DEPARTMENTS ONLY	40
IN EMPLOYEES ONLY	NULL

Two rows, and between them they are a complete statement about where the two tables disagree. 40 is in DEPARTMENTS only. The NULL is in EMPLOYEES only. Everything not listed agrees.

That is a reconciliation. What is in one system and not the other, both directions at once, with a label saying which direction each row came from. It is the shape of question that turns up constantly in real work. A source table against a target table, yesterday's load against today's, a warehouse against the system it was copied from.

Episode 26 built the same shape out of a FULL OUTER JOIN with a CASE expression and a COALESCE. This is the set operator spelling of it, and it is a noticeably shorter query. Both approaches reach the same place, and neither was timed, so nothing here is said about which one runs better.

Why the outer operator has ALL on it

UNION ALL is the right choice for the outer stack, not UNION. The two branches cannot overlap, because that is what MINUS guaranteed, and the SIDE label would keep them apart even if a value did repeat. Reaching for UNION here would add a deduplication pass with nothing to find and a row to lose, which is the lesson of Episode 34.

So which one do we reach for?

Level 3 has now given us two ways to ask most of these questions. So here is the short version of when each one is the better tool.

The question	Set operator	Join equivalent
Which values appear in both lists?	INTERSECT	An inner join, then DISTINCT on the key
Which values are in the first list only?	MINUS or EXCEPT	An anti-join. A left join, then WHERE … IS NULL, from Episode 25
Where do the two lists disagree, both directions?	MINUS both ways, stacked with UNION ALL	A full outer join with a CASE label, from Episode 26
I need columns from both sides, not just the keys	None of them. A set operator returns one list, not a pair of rows joined together	A join. This is the thing joins are for

The dividing line is clean enough to keep in your head. A set operator compares two lists and returns a list. A join combines two tables and returns wider rows.

So if what we want back is wider than either input, we want a join. If what we want back is a question about membership, these three are shorter and they say what they mean.

Key points

• INTERSECT returns the rows present in both branches. MINUS returns the rows of the first branch that are not in the second.
• EXCEPT and MINUS are one operator with two names. The two captured grids on this page are identical.
• INTERSECT returns distinct rows, and there is no ALL variant of it or of MINUS. The deduplication is not optional.
• MINUS is not symmetric. DEPARTMENTS MINUS EMPLOYEES returns 40. The other way round returns NULL. Different questions, not opposite answers.
• These operators compare every column we selected. Marcus is in department 10 but earns 96500.00, so his two-column row matched nothing and he is absent from the result.
• A set operator compares rows the way DISTINCT does, not the way a join's ON clause does. So NULL does not behave here the way Level 3 taught us to expect.
• Both directions of a MINUS, labelled and stacked with UNION ALL, is a reconciliation. What is in one system and not the other, both ways, in one result.
• That is the same answer Episode 26 built from a FULL OUTER JOIN, in a much shorter query. Nothing here was timed, so nothing is claimed about which is quicker.
• Set operators return a list. Joins return wider rows. If we need columns from both sides, no set operator will do it.

Next in this series

In the next post we will look at CONNECT BY. It is the last episode of Level 3, and it is the one where a table joins to itself over and over. We walk a hierarchy from a starting row all the way down, however many levels deep it goes.

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. No performance claims are made anywhere in this series. Nothing here was timed, so nothing about a set operator being faster or slower than the equivalent anti-join is said. The claim made above is only that they agree. Operator behaviour is described from the Snowflake set operator documentation.