Snowflake SQL Tutorial 29 - Joining Three or More Tables in Snowflake

Snowflake SQL · Episode 29

In this post we are going to cover joining three or more tables in Snowflake.

There is no new keyword for it. We write a second JOIN after the first one, with its own ON clause, and the result of the first join becomes the left-hand side of the second. That is a chain, and we can make it as long as we like.

We will start with a plain three-table join and find that two people are missing from it. Then we will find out who they are, fix one join, fix the other, and put the three row counts side by side. After that we will do the same thing on orders and customers, add up the groups to check the join lost nothing, and finish by writing a long chain as CTEs so every hop can be counted on its own.

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

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

Let's create some sample data

A chain of joins needs something to chain along, and EMPLOYEES gives us two such columns rather than one. Run the script below once if you do not already have these two tables.

Sample data
-- Both tables already exist if you have followed this level from the start.
-- Read the two marked columns in EMPLOYEES: they are the two links this episode
-- follows, and one of them points back at this same table.
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,            -- <<-- points OUT, at DEPARTMENTS.DEPT_ID
    MANAGER_ID      INT,            -- <<-- points BACK, at EMPLOYEES.EMP_ID
    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);
  • DEPT_ID points out, at DEPARTMENTS.DEPT_ID.
  • MANAGER_ID points back, at EMPLOYEES.EMP_ID, the same table it lives in.

So every employee sits on two hops. One to a department, and one to another employee. Follow both and we have a chain of three tables, even though only two tables exist.

Two values in that INSERT list decide everything that follows, and they are easy to read past. Daniel's MANAGER_ID is NULL, because he is the one at the top. Grace's DEPT_ID is NULL, because she has not been assigned one. Everyone else has both.

Count that list and the table is eight employees long. Hold on to that number, because this whole post is about where some of them go.

About the grids below

This episode did not re-capture a SELECT * of either table, so there is no grid of raw source rows here. The two NULLs above do get their own captured grid, two sections down, which is where they start to matter.

CUSTOMERS and ORDERS turn up in the second half of this page. Both come from the shared sample-data script for this course, and every value we use from them is visible in the grid that uses it.

Let's start by joining three tables

There is no special syntax for a third table. We write a second JOIN after the first, give it its own ON clause, and the result of the first join becomes the left-hand side of the second.

Query
SELECT E.FIRST_NAME AS EMPLOYEE, D.DEPT_NAME, M.FIRST_NAME AS MANAGER
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMP_ID
ORDER BY E.EMP_ID;
Result6 rows · 3 columns
EMPLOYEEDEPT_NAMEMANAGER
AishaEngineeringDaniel
MarcusEngineeringDaniel
LenaFinanceDaniel
TomasFinanceLena
PriyaMarketingDaniel
SofiaMarketingPriya

Have a look at the second JOIN. It names EMPLOYEES again, under the alias M. Snowflake treats E and M as two independent sources that happen to hold the same rows. That is why the aliases are compulsory here rather than just tidy. Without them there is no way to say which EMP_ID we mean.

Named once, explained later

A table joined to itself is a self join, and it is the whole subject of the next post. On this page it is only ever “a third table that happens to be the same table”.

Now the part worth stopping on. The setup script inserted eight employees. This result has six rows. Two people are missing, and nothing in the output says so. A join that drops rows gives us a shorter result, not a warning.

Now let's find out who went missing

Before we change anything, let's find out who is gone. The two links are the only thing that can fail, so we look for a NULL in either of them.

Query
SELECT EMP_ID, FIRST_NAME, DEPT_ID, MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL OR DEPT_ID IS NULL
ORDER BY EMP_ID;
Result2 rows · 4 columns
EMP_IDFIRST_NAMEDEPT_IDMANAGER_ID
6Daniel10NULL
8GraceNULL6

Two rows. Daniel has no MANAGER_ID and Grace has no DEPT_ID. They are missing for different reasons, at different points in the chain.

RowWhich link is NULLWhich join dropped itWhy
DanielMANAGER_IDThe second one, to EMPLOYEES MHe passed the first join, because he is in Engineering, and then had no manager ID to match
GraceDEPT_IDThe first one, to DEPARTMENTSNo department ID to match, and NULL matches nothing, so she never reached the second join at all

That is the whole reason this post exists. Two rows, two different failures, two different lines of SQL to change. Fixing one does nothing for the other. And if we only check the first join, we will conclude the fix did not work.

Let's fix the first join and see what happens

Grace was lost at the department join, so let's change that one to a LEFT JOIN. Same tables, same two ON clauses, same order.

Query
SELECT E.FIRST_NAME, D.DEPT_NAME, M.FIRST_NAME AS MANAGER
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMP_ID
ORDER BY E.EMP_ID;
Result7 rows · 3 columns
FIRST_NAMEDEPT_NAMEMANAGER
AishaEngineeringDaniel
MarcusEngineeringDaniel
LenaFinanceDaniel
TomasFinanceLena
PriyaMarketingDaniel
SofiaMarketingPriya
GraceNULLDaniel

Seven rows now. Grace is back, with a NULL department, exactly as a left join promises. And Daniel is still gone.

One small thing to notice before we go on: the first column is headed FIRST_NAME in this grid and EMPLOYEE in the one above it. That is an alias we dropped, not a different column.

Now read the query from the top and the surprise gets sharper. It opens with LEFT JOIN. It looks like a query that keeps every employee. It does not, because the second join is still a plain JOIN, and Daniel has no MANAGER_ID for it to match.

The sentence to remember

A LEFT JOIN preserves rows into the next join, not past it. Anything the chain does afterwards can throw them straight back out, and an inner join is exactly the thing that does.

There is a clue in the grid if we look for it. Daniel is in the MANAGER column of five of those seven rows, and he has no row of his own. The M side of the query can see him perfectly well. It is the E side that lost him.

Now let's fix the second one too

One keyword left to change. This time the only difference from the query above is that second JOIN becoming a LEFT JOIN.

Query
SELECT E.FIRST_NAME, D.DEPT_NAME, M.FIRST_NAME AS MANAGER
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
LEFT JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMP_ID
ORDER BY E.EMP_ID;
Result8 rows · 3 columns
FIRST_NAMEDEPT_NAMEMANAGER
AishaEngineeringDaniel
MarcusEngineeringDaniel
LenaFinanceDaniel
TomasFinanceLena
PriyaMarketingDaniel
DanielEngineeringNULL
SofiaMarketingPriya
GraceNULLDaniel

Eight rows. Everybody who was inserted. Daniel is back with a NULL manager, and that is not a gap in the data. It is the correct answer. He is the top of the reporting line, and NULL is how that fact looks in a result set.

Grace still has a NULL department, for the same reason she did before. Every NULL in that grid traces back to exactly one missing link in exactly one row of the source.

Let's put the three row counts side by side

Three results, three row counts. The same three tables, the same two ON clauses, the same three columns and the same ORDER BY every time. What changed was the join keywords.

First join (to DEPARTMENTS)Second join (to EMPLOYEES M)RowsWho is in it
JOINJOIN6Neither Grace nor Daniel
LEFT JOINJOIN7Grace, but still not Daniel
LEFT JOINLEFT JOIN8Everybody

The middle row is the one to take away. It is the shape of a bug report that wastes an afternoon: “I used a LEFT JOIN and rows are still missing.” The LEFT JOIN worked. Something further down the chain undid it.

So the join type travels along the chain, and the strictest link decides the result. When a chain loses rows, check every link rather than the first one.

The other way to undo a LEFT JOIN

The same thing happens through a WHERE clause rather than a join keyword. A condition on a column from the right-hand table of a LEFT JOIN is false for every invented NULL, so it quietly removes the rows the left join just preserved, turning the whole thing back into an inner join. The fix is the same: put that condition in the ON clause, or test for NULL explicitly.

Now let's try the same shape on data that looks like work

Chains of two links are the common case in real reporting. A fact table joined to a dimension, then filtered and summarised. So let's join ORDERS to CUSTOMERS and cut it down to the big orders.

Query
SELECT C.CUSTOMER_NAME, C.COUNTRY, O.ORDER_ID, O.STATUS, O.AMOUNT
FROM ORDERS O
JOIN CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.AMOUNT > 7000
ORDER BY O.AMOUNT DESC;
Result12 rows · 5 columns
CUSTOMER_NAMECOUNTRYORDER_IDSTATUSAMOUNT
Meridian HealthUSA1037SHIPPED13100.00
Meridian HealthUSA1032SHIPPED12850.00
Meridian HealthUSA1025SHIPPED11200.00
Meridian HealthUSA1020SHIPPED10400.00
Northwind TradersUSA1040SHIPPED9640.00
Meridian HealthUSA1015SHIPPED9250.00
Northwind TradersUSA1035SHIPPED8820.00
Alpine LogisticsNULL1026SHIPPED8340.00
Northwind TradersUSA1011SHIPPED8100.00
Alpine LogisticsNULL1007SHIPPED7890.00
Northwind TradersUSA1029SHIPPED7460.00
Alpine LogisticsNULL1019SHIPPED7015.00

Twelve rows and five columns. Two tables and one join this time, with the customer's name and country coming from one side and the order details from the other, filtered to amounts above 7000.

The row that matters most is easy to miss. Alpine Logistics has a NULL country.

That NULL is stored data, not a join artifact. This is an inner join, and an inner join cannot invent a NULL. It only returns rows that matched on both sides. The customer exists and the country field is simply empty. That distinction is worth keeping straight, because the next query groups by that column.

Now let's aggregate across the join and reconcile

Let's summarise across that join. Orders and total value per country.

Query
SELECT C.COUNTRY, COUNT(*) AS ORDERS, SUM(O.AMOUNT) AS TOTAL
FROM ORDERS O
JOIN CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.COUNTRY
ORDER BY C.COUNTRY NULLS LAST;
Result4 rows · 3 columns
COUNTRYORDERSTOTAL
Canada924711.50
UK315870.00
USA24128280.50
NULL536565.00

Four groups, and one of them has NULL for a country. That is the group holding Alpine Logistics' orders, and it belongs in the report. Those orders happened, the money is real, and the only thing missing is a country label. GROUP BY treats all the NULLs as one group, and ORDER BY ... NULLS LAST puts it at the bottom, where it reads as a residual rather than as a country called nothing.

Now let's add up the groups

Here is a check worth running after any join, and it takes five seconds. Add the four counts in the ORDERS column above: 9 + 3 + 24 + 5 = 41.

ORDERS holds 41 rows. That figure is in this course's captured source-table counts, and it is not something this page re-ran. The groups add up to the table, so every order is in exactly one group and the join lost nothing.

Why bother

If that sum had come out short, the join ate something. An order whose CUSTOMER_ID matches no customer, most likely. And no part of the result would have told us. A total that is quietly too small is the most dangerous output a report can produce, because it looks exactly like a correct one.

The check generalises. The group counts from an aggregation over a join should reconcile against the row count of the table we were driving from. When they do not, we have found a join problem before our reader did.

Can we make a long chain readable? Let's use CTEs

Two joins fit on a screen. Five or six do not, and a chain we cannot read is a chain whose row count nobody checks. One answer is to name each hop with a WITH clause, so every step becomes something we can run on its own.

Query
WITH STAFF AS (
  SELECT EMP_ID, FIRST_NAME, DEPT_ID, MANAGER_ID FROM EMPLOYEES
), WITH_DEPT AS (
  SELECT S.EMP_ID, S.FIRST_NAME, S.MANAGER_ID, D.DEPT_NAME
  FROM STAFF S JOIN DEPARTMENTS D ON S.DEPT_ID = D.DEPT_ID
)
SELECT W.FIRST_NAME AS EMPLOYEE, W.DEPT_NAME, M.FIRST_NAME AS MANAGER
FROM WITH_DEPT W
JOIN EMPLOYEES M ON W.MANAGER_ID = M.EMP_ID
ORDER BY W.EMP_ID;
Result6 rows · 3 columns
EMPLOYEEDEPT_NAMEMANAGER
AishaEngineeringDaniel
MarcusEngineeringDaniel
LenaFinanceDaniel
TomasFinanceLena
PriyaMarketingDaniel
SofiaMarketingPriya

That is the same result as the three-table join at the top of this page. The same six rows, in the same order, with the same column names. Nothing about the data changed. Only the arrangement did.

What we gain is the ability to debug by row count. Run STAFF on its own and count it. Run WITH_DEPT and count it. The step where the number drops is the join that is dropping rows, and we found it by looking rather than by guessing.

More on WITH later

Common table expressions get proper treatment later in this course. They are introduced here because a long join chain is the first place most people actually need them. Not for cleverness, but so that each link can be counted separately.

Key points

  • Joining three tables is writing JOIN twice. The result of the first join is the left-hand side of the second, and the syntax barely changes.
  • A table can appear twice in one query under two aliases. The aliases are then compulsory, because a bare column name no longer identifies which copy you mean.
  • Rows can be dropped at different points in a chain for different reasons — Grace at the first join, Daniel at the second — so fixing one does not fix the other.
  • The join type travels along the chain. Plain and plain gave 6 rows; left and plain gave 7; left and left gave 8. The join keywords are what changed.
  • One plain JOIN anywhere in a chain can undo every LEFT JOIN above it, silently. A query that opens with LEFT JOIN is not a query that keeps every row.
  • A WHERE condition on the right-hand table of a LEFT JOIN does the same damage, for the same reason.
  • An inner join cannot invent a NULL, so a NULL in an inner-join result is stored data — which makes a NULL group in an aggregation real, not an artifact.
  • After any join, add up the group counts and reconcile them against the driving table: 9 + 3 + 24 + 5 = 41, and ORDERS has 41 rows.
  • When a chain gets long, name each hop as a CTE — same result, and every step becomes something you can count on its own.
Next in this series

In the next post we will cover self joins. The trick we used once here and named in passing, done properly: one table joined to itself, why the aliases are the whole technique, and what happens to the row at the top of the hierarchy.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the shared sample-data script for this course. The EMPLOYEES, DEPARTMENTS, CUSTOMERS and ORDERS data is invented demo data created by that script. The ORDERS row count used in the reconciliation comes from this course's captured source-table counts rather than from a grid on this page, and is labelled as such where it is used. No performance or benchmark claims are made anywhere in this series — nothing here was timed, and no join order is described as faster than another. Join behaviour is described from the Snowflake JOIN documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.