Snowflake SQL Tutorial 24 - INNER JOIN in Snowflake

Snowflake SQL · Episode 24

In this post we are going to cover the INNER JOIN in Snowflake. It is the first post of Level 3, which is all about joins.

Every query in this course so far has read one table. A join reads two. We tell Snowflake which column the two tables have in common, and Snowflake matches the rows up on it. An inner join then keeps the rows that found a match and drops the rows that did not.

We will look at both tables first, then join them, then prove which rows the join threw away. At the end we will put a GROUP BY on top of the join and watch a whole department disappear out of a report, 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 24 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's walk through it before we write any SQL
3. Now let's write it as SQL
4. Now let's prove what the join dropped
5. Now let's drop the word INNER
6. Now let's take columns from both sides
7. Let's try the same rule on two different tables
8. Now let's put a GROUP BY on top of the join

Let's create some sample data

A join is a statement about how the rows of one table relate to the rows of another. We cannot check that statement without seeing both sides, so both sides go on screen before anything is joined.

EMPLOYEES already exists. It was created in episode 1 and every episode since has queried it. DEPARTMENTS is new, and this is the whole of it. Run it once; every episode in this level reuses it.

Setup — run once

-- DEPARTMENTS is the second table Level 3 needs. EMPLOYEES was created
-- back in Episode 1; this adds the table the joins match it against.
-- Run it once; every episode in Level 3 reuses it.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

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

Now let's look at what we just created.

Query

SELECT DEPT_ID, DEPT_NAME, LOCATION FROM DEPARTMENTS ORDER BY DEPT_ID;

Result4 rows · 3 columns

DEPT_ID	DEPT_NAME	LOCATION
10	Engineering	Charlotte
20	Finance	Charlotte
30	Marketing	Raleigh
40	Operations	Raleigh

A department ID, a name and a location. There is a budget column as well, which this query leaves out and a later section brings back.

And here is the other side, cut down to the three columns this episode cares about.

Query

SELECT EMP_ID, FIRST_NAME, DEPT_ID FROM EMPLOYEES ORDER BY EMP_ID;

Result8 rows · 3 columns

EMP_ID	FIRST_NAME	DEPT_ID
1	Aisha	10
2	Marcus	10
3	Lena	20
4	Tomas	20
5	Priya	30
6	Daniel	10
7	Sofia	30
8	Grace	NULL

One column appears in both tables: DEPT_ID. That shared column is what the join is built on. In DEPARTMENTS it identifies the department. In EMPLOYEES it says which department the person belongs to.

Two rows in those grids decide every count on this page, and they are both easy to read past.

Row	Which table	Why it has nothing to match
Grace, DEPT_ID is NULL	EMPLOYEES	NULL is not equal to anything — not to a department ID, and not to another NULL
Operations, DEPT_ID = 40	DEPARTMENTS	No employee row carries 40. Scan the DEPT_ID column above: nobody is in it

Why start here

Reading both tables before you join them is a habit worth keeping for real work too. Almost every surprising join result is explained by a row that looks unremarkable in its own table — a NULL key, or a value that exists on one side and not the other.

Let's walk through it before we write any SQL

Put EMPLOYEES on the left and DEPARTMENTS on the right. A join works through the left table one row at a time, and for each row it asks one question: is there a row on the right that matches?

1. Aisha is in department 10. Department 10 exists, so the two rows are connected.
2. Marcus is in 10 as well. Same department, same connection, a second time.
3. Daniel is in 10 too, so that is a third.
4. Lena and Tomas are in 20. Priya and Sofia are in 30. Both departments exist, so all four connect.
5. Grace has no department ID at all, so there was never anything to look for.
6. And nothing on the left ever points at Operations.

Then the rule, which is the entire concept. Keep what is connected, drop the rest. An inner join returns only the rows that found a partner.

That is why the two rows in the table above matter so much. They are the only rows on either side that make no connection, and every count on the rest of this page is a consequence of them.

Now let's write it as SQL

The same idea in SQL. We name the columns we want from either table, give each table an alias, and then say what counts as a match.

Query

SELECT E.FIRST_NAME, E.DEPT_ID, D.DEPT_NAME, D.LOCATION
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
ORDER BY E.EMP_ID;

Result7 rows · 4 columns

FIRST_NAME	DEPT_ID	DEPT_NAME	LOCATION
Aisha	10	Engineering	Charlotte
Marcus	10	Engineering	Charlotte
Lena	20	Finance	Charlotte
Tomas	20	Finance	Charlotte
Priya	30	Marketing	Raleigh
Daniel	10	Engineering	Charlotte
Sofia	30	Marketing	Raleigh

Eight employees went in. Seven rows came out.

The ON clause is the whole game. ON E.DEPT_ID = D.DEPT_ID is the definition of a match, and nothing else in the statement decides it. Change that one expression and we change which rows survive.

Have a look at how the result mixes columns from both tables in a single row. FIRST_NAME and DEPT_ID came from EMPLOYEES, while DEPT_NAME and LOCATION came from DEPARTMENTS. The alias prefix on each column is how we keep track of which side is which, and it is how Snowflake keeps track too.

Qualify your columns

DEPT_ID exists in both tables, so SELECT DEPT_ID on its own would be ambiguous. Qualifying every column with its table alias — E.DEPT_ID rather than DEPT_ID — costs two characters and removes the whole class of problem.

Now let's prove what the join dropped

A row count we cannot account for is a row count we do not understand. Two rows went missing, and we can produce each one on its own.

The employee with no department

Query — who has no DEPT_ID at all?

SELECT EMP_ID, FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID IS NULL;

Result1 row · 3 columns

EMP_ID	FIRST_NAME	DEPT_ID
8	Grace	NULL

One row: Grace, with DEPT_ID set to NULL. There was no department ID to compare, so the ON clause was never true for her and the inner join had nothing to return.

The department with no employees

Query — which department has nobody in it?

SELECT D.DEPT_ID, D.DEPT_NAME
FROM DEPARTMENTS D
WHERE NOT EXISTS (SELECT 1 FROM EMPLOYEES E WHERE E.DEPT_ID = D.DEPT_ID);

Result1 row · 2 columns

DEPT_ID	DEPT_NAME
40	Operations

One row: Operations, department 40. The department is real, its row is right there in DEPARTMENTS, and not one employee points at it.

That accounts for the arithmetic completely. Eight employee rows, minus the one with a NULL key, is the seven rows the join returned. And on the other side, one of the four departments contributed nothing at all.

Now let's drop the word INNER

INNER JOIN and plain JOIN mean exactly the same thing in Snowflake. Let's leave the keyword out and see.

Query — same join, keyword omitted

SELECT E.FIRST_NAME, D.DEPT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
ORDER BY E.EMP_ID;

Result7 rows · 2 columns

FIRST_NAME	DEPT_NAME
Aisha	Engineering
Marcus	Engineering
Lena	Finance
Tomas	Finance
Priya	Marketing
Daniel	Engineering
Sofia	Marketing

The same rows, from the same match, with two columns selected instead of four.

Write INNER JOIN anyway. Later in this level you will be writing LEFT JOIN, RIGHT JOIN and FULL JOIN in the same files, and a bare JOIN sitting among them reads like someone forgot to finish the line rather than like a deliberate choice.

Now let's take columns from both sides

A join does more than confirm that a match exists. It hands us columns from both tables side by side, which is the reason we wanted the join in the first place.

BUDGET is the column the first DEPARTMENTS query left out. Let's put it in a join, next to each employee's salary.

Query

SELECT E.FIRST_NAME, E.SALARY, D.DEPT_NAME, D.BUDGET
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
ORDER BY D.DEPT_NAME, E.FIRST_NAME;

Result7 rows · 4 columns

FIRST_NAME	SALARY	DEPT_NAME	BUDGET
Aisha	118000.00	Engineering	2400000.00
Daniel	131000.00	Engineering	2400000.00
Marcus	96500.00	Engineering	2400000.00
Lena	104000.00	Finance	1100000.00
Tomas	87250.00	Finance	1100000.00
Priya	96500.00	Marketing	750000.00
Sofia	71400.00	Marketing	750000.00

FIRST_NAME and SALARY come from EMPLOYEES. DEPT_NAME and BUDGET come from DEPARTMENTS. One row, two tables.

Now look at Engineering. 2400000.00 appears three times, once against each engineer. That repetition is sometimes read as a bug, and it is not one.

Repeated, not duplicated

The budget is a property of the department, not of the person, and the result has one row per employee. Every Engineering row therefore carries the same department budget, correctly. What you must not do is add that column up. SUM(D.BUDGET) over this result would count the Engineering budget three times and give you a total that exists nowhere in either table.

Let's try the same rule on two different tables

None of this is about employees and departments. A join is a match on a shared column, and the tables can be anything.

This course's sample data also has CUSTOMERS and ORDERS. The demo script's own row counts put CUSTOMERS at 10 rows and ORDERS at 41, which is too many to show here and beside the point anyway. What matters is that CUSTOMER_ID appears in both, so CUSTOMER_ID is what the join matches on.

Query — a different pair of tables, the same join

SELECT C.CUSTOMER_NAME, O.ORDER_ID, O.AMOUNT
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE O.AMOUNT > 8000
ORDER BY O.AMOUNT DESC;

Result9 rows · 3 columns

CUSTOMER_NAME	ORDER_ID	AMOUNT
Meridian Health	1037	13100.00
Meridian Health	1032	12850.00
Meridian Health	1025	11200.00
Meridian Health	1020	10400.00
Northwind Traders	1040	9640.00
Meridian Health	1015	9250.00
Northwind Traders	1035	8820.00
Alpine Logistics	1026	8340.00
Northwind Traders	1011	8100.00

The customer name comes from one table. The order ID and amount come from the other. The WHERE clause then keeps only the orders above 8000, which is an ordinary filter doing an ordinary job on top of the join.

Same keyword, same ON clause, same rule. That is all a join ever is.

Now let's put a GROUP BY on top of the join

This is the part that costs people real money. Put a GROUP BY on top of that first join and we have a headcount and payroll report per department. It is the kind of query that gets written once and then emailed every month.

Query — headcount and payroll per department

SELECT D.DEPT_NAME, COUNT(*) AS HEADCOUNT, SUM(E.SALARY) AS PAYROLL
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
GROUP BY D.DEPT_NAME
ORDER BY D.DEPT_NAME;

Result3 rows · 3 columns

DEPT_NAME	HEADCOUNT	PAYROLL
Engineering	3	345500.00
Finance	2	191250.00
Marketing	2	167900.00

Engineering, Finance, Marketing. Three rows.

Now scroll back to the first grid on this page. The company has four departments. Operations is not in this report.

What DEPARTMENTS holds	What the report shows	What tells you
4 rows	3 rows	Nothing at all

Watch out

There is no error. No warning. No NULL row with a zero headcount. The query succeeded and the report is wrong.
The join dropped Operations before GROUP BY ever ran, so by the time the grouping happened there was nothing left to group. Nothing survives into the result to hint that a department went missing.

Send that table to somebody who runs Operations and they will spot it in a second. Feed it into a company-wide payroll total and nobody will ever spot it. The total is simply quietly short.

The fix is an outer join, which is the next post. The habit matters more than the fix, though. When you see a join in a query, ask what it dropped.

Key points

• An INNER JOIN returns only the rows that match on both sides. Everything unmatched is discarded, silently.
• The ON clause is the definition of a match. Nothing else in the statement decides which rows survive.
• A NULL key matches nothing — not a value, and not another NULL. Grace is dropped for that reason alone.
• A row on the right with no partner on the left disappears just as quietly. Operations is a real department that contributed no rows.
• INNER JOIN and plain JOIN are the same thing. Write INNER so your intent is on the page.
• A join hands you columns from both tables in one row, so a one-per-department value repeats once per employee. That is correct, and it is also why you must not SUM it.
• Qualify every column with its table alias. DEPT_ID exists in both tables and unqualified references are ambiguous.
• GROUP BY runs after the join, so a group that the join dropped cannot appear in the grouped result — and nothing in that result says so.
• When you read a query with a join in it, the useful question is not what it returns but what it left out.

Next in this series

Episode 25 — LEFT and RIGHT OUTER JOIN. How to keep the rows an inner join throws away, what Snowflake puts in the columns that have no match, and the WHERE clause that turns an outer join back into an inner one without telling you.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES, DEPARTMENTS, CUSTOMERS and ORDERS data is invented demo data created by that script. No performance claims are made anywhere in this series — nothing about join order, build side or what Snowflake reads first was measured, so nothing about it is said. Clause behaviour is described from the Snowflake JOIN documentation.