Snowflake SQL Tutorial 26 - FULL OUTER JOIN in Snowflake

Snowflake SQL · Episode 26

In this post we are going to cover the FULL OUTER JOIN in Snowflake.

A FULL OUTER JOIN keeps every row from both tables. The rows that matched come back once each. The rows that matched nothing come back too, with NULL standing in for the side that had no row to give.

We will start by counting what all four join types return over the same two tables, then run the full outer join itself, then use it for the job it is really for: showing what is in one table and not the other, both directions at once. At the end we will do one subtraction that tells us how much of a join does not reconcile.

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

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

On this page

1. Let's look at the two tables we are joining
2. Let's count all four joins in one query
3. Now let's run the FULL OUTER JOIN itself
4. Now let's show only the rows that did not line up
5. Let's label which side each row came from
6. Let's try it on orders and customers
7. So what does FULL minus INNER tell us?
8. Which join should we actually reach for?

Let's look at the two tables we are joining

We are using the same two tables as the last two posts. EMPLOYEES has eight rows and DEPARTMENTS has four. Both of them are on the page for Episode 24 if you want to see the rows.

Two of those rows do all the work on this page. Grace has no DEPT_ID. Operations has no employees.

An inner join dropped both of them. A left join kept Grace and dropped Operations. A right join did the opposite. A FULL OUTER JOIN keeps both, and that is the last rung of the ladder.

Let's count all four joins in one query

Before we write the join, let's have Snowflake count the four of them for us. Four scalar subqueries over the same two tables, and the only thing that changes between them is the join keyword.

Query — every join type, one row of counts

SELECT
  (SELECT COUNT(*) FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID)             AS INNER_ROWS,
  (SELECT COUNT(*) FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID)        AS LEFT_ROWS,
  (SELECT COUNT(*) FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID)       AS RIGHT_ROWS,
  (SELECT COUNT(*) FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID)  AS FULL_ROWS;

Result1 row · 4 columns

INNER_ROWS	LEFT_ROWS	RIGHT_ROWS	FULL_ROWS
7	8	8	9

One row back, and the whole level is in it.

Join	Which rows it keeps	Rows
INNER JOIN	Only what matched on both sides	7
LEFT JOIN	Everything matched, plus Grace	8
RIGHT JOIN	Everything matched, plus Operations	8
FULL OUTER JOIN	Everything matched, plus Grace, plus Operations	9

There is nothing to memorise there. Each number is the matched rows plus whichever unmatched rows that join type promised to keep. The arithmetic is the definition.

Now let's run the FULL OUTER JOIN itself

Here is the join. Notice what we are asking for in the select list. We want the join key from both tables, E_DEPT from EMPLOYEES and D_DEPT from DEPARTMENTS. That is the only way to see which side a row came from.

Query

SELECT E.FIRST_NAME, E.DEPT_ID AS E_DEPT, D.DEPT_ID AS D_DEPT, D.DEPT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
ORDER BY D.DEPT_ID, E.EMP_ID;

Result9 rows · 4 columns

FIRST_NAME	E_DEPT	D_DEPT	DEPT_NAME
Aisha	10	10	Engineering
Marcus	10	10	Engineering
Daniel	10	10	Engineering
Lena	20	20	Finance
Tomas	20	20	Finance
Priya	30	30	Marketing
Sofia	30	30	Marketing
NULL	NULL	40	Operations
Grace	NULL	NULL	NULL

Nine rows. The first seven are the matched ones, and the two key columns agree on every one of them. Those rows were never in doubt. They come back from all four join types.

Then come the two rows that make this join different. Have a look at the Operations row. It has a department ID in D_DEPT and NULL in both FIRST_NAME and E_DEPT. There was no employee to put there, so the join made the employee side up.

Grace's row is the other way round. Her name is there, and D_DEPT and DEPT_NAME are NULL because no department matched her. Her E_DEPT is NULL too, but that one is not the join's doing. It is NULL in the EMPLOYEES table itself, and that is exactly why she matched nothing.

So we have two invented rows, and the NULLs the join invented are on opposite sides. That is the whole of what FULL adds.

Join	Grace	Operations	Rows
LEFT JOIN	kept, department columns invented	dropped	8
RIGHT JOIN	dropped	kept, employee columns invented	8
FULL OUTER JOIN	kept, department columns invented	kept, employee columns invented	9

OUTER is optional

OUTER is optional here too. FULL JOIN and FULL OUTER JOIN mean the same thing. Write the word OUTER anyway. It is a useful reminder that NULLs are about to turn up on both sides.

Watch out

Once a join is FULL, no column in the result is safe to assume is populated. Not a primary key, and not even the join key. Every WHERE clause, every COUNT and every expression after it has to cope with NULLs arriving from either direction.

Now let's show only the rows that did not line up

This is the job FULL OUTER JOIN is really for. We keep everything, then filter down to the rows where either side came back NULL.

Query — the unmatched rows from both tables at once

SELECT E.FIRST_NAME, E.DEPT_ID AS E_DEPT, D.DEPT_ID AS D_DEPT, D.DEPT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
WHERE E.EMP_ID IS NULL OR D.DEPT_ID IS NULL;

Result2 rows · 4 columns

FIRST_NAME	E_DEPT	D_DEPT	DEPT_NAME
Grace	NULL	NULL	NULL
NULL	NULL	40	Operations

Two rows. Grace and Operations. One orphan from each table, in one result, from one query.

Episode 25 found each of those with its own anti-join, one query per direction. Here we get both directions in one pass. That is the shape of a reconciliation: what is in one system and not the other, both ways round.

Test a key, not just any column

Look at which columns the WHERE clause tests: E.EMP_ID IS NULL OR D.DEPT_ID IS NULL.
E.EMP_ID is the employees table's key and it is never NULL in the source, so E.EMP_ID IS NULL can only mean the join manufactured that side. Testing E.DEPT_ID IS NULL instead would have been wrong. That column is genuinely nullable. It is NULL for Grace in the table itself, so it cannot tell “no match” apart from “matched, and the stored value is NULL”.

Let's label which side each row came from

A reconciliation reads better when each row says where it came from. A full outer join gives us enough information to work that out inside the query.

Query — every row, tagged with its origin

SELECT COALESCE(E.DEPT_ID, D.DEPT_ID) AS DEPT_KEY,
       E.FIRST_NAME,
       D.DEPT_NAME,
       CASE WHEN E.EMP_ID IS NULL THEN 'DEPARTMENT ONLY'
            WHEN D.DEPT_ID IS NULL THEN 'EMPLOYEE ONLY'
            ELSE 'BOTH' END AS SIDE
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
ORDER BY SIDE, DEPT_KEY;

Result9 rows · 4 columns

DEPT_KEY	FIRST_NAME	DEPT_NAME	SIDE
10	Aisha	Engineering	BOTH
10	Marcus	Engineering	BOTH
10	Daniel	Engineering	BOTH
20	Lena	Finance	BOTH
20	Tomas	Finance	BOTH
30	Priya	Marketing	BOTH
30	Sofia	Marketing	BOTH
40	NULL	Operations	DEPARTMENT ONLY
NULL	Grace	NULL	EMPLOYEE ONLY

All nine rows, and each one is carrying a SIDE label: matched on both sides, department only, or employee only.

Two things in that query are worth naming. The first is CASE. It is reading the two key columns and writing the label. It gets a whole post of its own later in this course. Here it is only choosing between three strings.

The second is COALESCE(E.DEPT_ID, D.DEPT_ID). After a full outer join there is no single column holding the key. The employee side is NULL for rows that exist only in DEPARTMENTS, and the department side is NULL for rows that exist only in EMPLOYEES. COALESCE takes the first of the two that is not NULL and hands us one usable key column back.

Why COALESCE is not decoration

That detail is easy to skip and expensive to miss. If you GROUP BY E.DEPT_ID over a full outer join, every department-only row collapses into a single NULL group, no matter how many different departments were in it. Group by the COALESCEd key instead.

Let's try it on orders and customers

Now a different pair of tables, and a prediction these queries got wrong before they were run. The correction is the useful part, so it is staying on the page.

The tables are ORDERS and CUSTOMERS. Every order belongs to a real customer. So the reasoning went like this: there are no orphan orders, therefore a full outer join should return the same count as an inner join. Let's run the inner join first.

Query — INNER JOIN, counted

SELECT COUNT(*) AS INNER_ROWS
FROM ORDERS O
JOIN CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID;

Result1 row · 1 column

INNER_ROWS
41

Now the full outer join, over the same two tables and the same ON clause.

Query — FULL OUTER JOIN, counted

SELECT COUNT(*) AS FULL_ROWS
FROM ORDERS O
FULL OUTER JOIN CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID;

Result1 row · 1 column

FULL_ROWS
43

Not the same. The prediction was wrong, and the reason is something Episode 25 already showed us. The reasoning only covered orphans on the ORDERS side, and there are none. But two customers have never placed an order, and a full outer join keeps a row for each of them.

Every order having a customer does not mean every customer has an order. A foreign key constrains one direction. A full outer join is interested in both.

So what does FULL minus INNER tell us?

We have now counted both pairs of tables twice, so let's put the four numbers side by side.

Tables	INNER	FULL	Difference	The rows behind it
EMPLOYEES and DEPARTMENTS	7	9	2	Grace, and Operations. Both on this page
ORDERS and CUSTOMERS	41	43	2	Copper Kettle Ltd and Sable Interiors, the two customers with no orders that Episode 25 found

The difference is the same both times, and that is not a coincidence. An inner join returns the matched rows. A full outer join returns the matched rows plus the unmatched rows from each side. Subtract one from the other and what is left is the number of rows that matched nothing, both sides added together.

So that is a cheap diagnostic. Two counts and one subtraction tell us how much of a join does not reconcile, before we read a single row of it. And if the answer is zero, the join type does not change the answer at all.

What it does not tell you

Useful as it is, the difference is a total. It will not tell you which side the unmatched rows came from, or which rows they were. For that you want the reconciliation query from earlier on this page, or the labelled version after it.

Which join should we actually reach for?

FULL OUTER JOIN gives the most complete answer, which is not the same as the right one. So let's choose by the question we are asking.

• We only want rows that exist on both sides, and anything unmatched is genuinely not part of the answer → INNER JOIN. Just know what it dropped.
• One table is the thing we are reporting on, and the other is extra detail that may be missing → LEFT JOIN, with the reported table on the left.
• Neither table is the authority, and we need to see what each one has that the other does not → FULL OUTER JOIN.

Reaching for FULL out of habit costs us something, and it is not speed. Nothing on this page was timed. It is that FULL changes the answer. It puts invented NULLs into columns that could never have been NULL before, on both sides at once, and every filter and aggregate after it has to be written knowing that.

The habit from Episode 24 still applies. When we see a join, we ask what it dropped. FULL OUTER JOIN is the only one where the answer is always nothing. So with this one the question becomes what it added.

Key points

• FULL OUTER JOIN keeps every row from both tables: the matched rows, plus the unmatched rows from each side.
• That means NULLs appear on both sides of the result, in different rows. LEFT and RIGHT can only invent them on one.
• The four counts over the same two tables are 7, 8, 8 and 9. One captured row makes the whole level checkable.
• OUTER is optional. FULL JOIN and FULL OUTER JOIN are identical.
• Keep everything, then filter to the rows where either side is NULL, and you have a reconciliation: both directions of “what is missing” in one query.
• Test a key column for NULL, never a nullable one, or you cannot tell “no match” from a stored NULL.
• After a full outer join no single column holds the join key. COALESCE both sides before you group by it.
• FULL minus INNER is the number of rows that matched nothing, both sides added together. It came to 2 for each pair of tables on this page.
• A foreign key constrains one direction only. Every order having a customer does not mean every customer has an order.

Next in this series

In the next post we will look at the CROSS JOIN: every row of one table against every row of the other, what that multiplies out to, and the missing ON clause that writes one by accident.

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 prediction described above was written before the queries were run and corrected by the captured output, which is left on the page rather than tidied away. No performance claims are made anywhere in this series — nothing about FULL being slower or faster than INNER was measured, so nothing about it is said. Clause behaviour is described from the Snowflake JOIN documentation.