Snowflake SQL Tutorial 30 - Self Joins in Snowflake

Snowflake SQL · Episode 30

In this post we are going to cover self joins in Snowflake.

A self join is a join where both sides are the same table. We write the table name in the FROM clause, then write it again, and we give each one its own alias so we can tell them apart. There is no new keyword to learn. Snowflake treats the two aliases as two independent tables that happen to hold the same rows.

We will use it for two different jobs. First we will follow a column that points back into its own table, which is how we put every employee next to their manager. Then we will use it to compare rows of a table against other rows of the same table, which is where the classic wrong answer lives. We will see that wrong answer, count it, and fix it.

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

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

On this page

1. Let's create some sample data
2. Let's write the same table twice
3. Now let's get Daniel back with a LEFT JOIN
4. Do we really need the aliases?
5. Now let's use a self join for something else
6. Let's fix both problems with one operator
7. Now let's ask two questions we could not ask before
8. Can we go two levels deep?

Let's create some sample data

Everything on this page runs against EMPLOYEES, the same table the rest of this course uses. Copy the script below into a worksheet and run it once.

Sample data

-- The shared sample data for this course. Run it once; every episode
-- in the series queries these same tables.
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');

A self join needs one thing from that table, and only one. It needs a column whose values are meant to be found in another column of the same table. MANAGER_ID is that column.

Let's put it next to the key it points at.

Query

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

Result8 rows · 3 columns

EMP_ID	FIRST_NAME	MANAGER_ID
1	Aisha	6
2	Marcus	6
3	Lena	6
4	Tomas	3
5	Priya	6
6	Daniel	NULL
7	Sofia	5
8	Grace	6

Eight rows and three columns. Now read Aisha's row. Her MANAGER_ID is 6. Look down the EMP_ID column for 6 and we land on Daniel. So Aisha reports to Daniel, and we found that out without leaving this table.

Have a look at Daniel's own row too. His MANAGER_ID is NULL. Nobody is above him, so the pointer has nowhere to go. Hold on to that NULL. It decides the row count of the next two queries.

The shape, not the feature

A column like this is often called a self-referencing foreign key. Nothing about it is special to Snowflake, and nothing about it needs a declared constraint. All that matters is that the values in one column are meant to be found in another column of the same table.

Let's write the same table twice

To follow that pointer we put EMPLOYEES in the FROM clause, then put it there again, and give each one an alias. E is the employee. M is the manager.

Query

SELECT E.FIRST_NAME AS EMPLOYEE, M.FIRST_NAME AS MANAGER
FROM EMPLOYEES E
JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMP_ID
ORDER BY E.EMP_ID;

Result7 rows · 2 columns

EMPLOYEE	MANAGER
Aisha	Daniel
Marcus	Daniel
Lena	Daniel
Tomas	Lena
Priya	Daniel
Sofia	Priya
Grace	Daniel

Seven rows, and the table has eight. One employee did not come back, and the grid tells us which one. There is no row for Daniel.

That is the NULL we were told to hold on to. Daniel's MANAGER_ID is NULL, and NULL is equal to nothing, so his half of the ON clause never matched.

Notice there is no new keyword anywhere in that query. ASOF, NATURAL and CROSS are join types. “Self join” is not a join type. It is an ordinary inner join that happens to have the same table on both sides.

And the row we lost was lost by the ordinary inner-join rule this level opened with. It is just easier to miss here, because the row that goes missing is the most senior person in the company.

Now let's get Daniel back with a LEFT JOIN

The fix is the one from the outer-join post, and only one word changes.

Query

SELECT E.FIRST_NAME AS EMPLOYEE, M.FIRST_NAME AS MANAGER
FROM EMPLOYEES E
LEFT JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMP_ID
ORDER BY E.EMP_ID;

Result8 rows · 2 columns

EMPLOYEE	MANAGER
Aisha	Daniel
Marcus	Daniel
Lena	Daniel
Tomas	Lena
Priya	Daniel
Daniel	NULL
Sofia	Priya
Grace	Daniel

Eight rows now, and Daniel is in the result with NULL in the MANAGER column.

That NULL is doing real work for us. It is how the result says this person has no manager. An inner join could only say that by leaving him out.

Watch out

When we self join a hierarchy, the row an inner join drops is almost always the root. That is the row a reader is most likely to notice is missing from an org chart, and least likely to notice is missing from a count. Reach for LEFT JOIN by default when you are following a pointer that is allowed to be NULL.

Do we really need the aliases?

It is tempting to read E and M as shorthand that keeps the query tidy. Let's write the same join without them and see what happens.

Query

SELECT FIRST_NAME, FIRST_NAME
FROM EMPLOYEES JOIN EMPLOYEES ON EMPLOYEES.MANAGER_ID = EMPLOYEES.EMP_ID;

Query failed — Snowflake returned an error

002027 (42601): SQL compilation error:
duplicate alias 'EMPLOYEES'

That is Snowflake's actual message, captured from a real run. It does not pick a side for us, and it does not warn and carry on. It refuses to compile.

Have a look at what the error complains about. Not the ambiguous column list. The duplicate alias.

Here is why. When we name a table in a FROM clause without an alias, the table name becomes the alias. So this query asked for two tables both called EMPLOYEES. Giving each one an alias is not decoration. It is the only thing that makes them separately addressable.

A good error

This is the one error in the whole of Level 3 that is genuinely helpful on first reading. An unaliased self join cannot be written at all, so there is no silent-wrong-answer version of this mistake to worry about.

Now let's use a self join for something else

Everything so far followed a pointer. The other use of a self join has nothing to do with pointers. It is comparing rows of a table against other rows of the same table.

Say we want pairs of colleagues who share a department. The obvious thing to try is joining the table to itself on DEPT_ID. Let's restrict it to department 10 so the whole result fits on a screen.

Query

SELECT E1.FIRST_NAME AS PERSON_A, E2.FIRST_NAME AS PERSON_B, E1.DEPT_ID
FROM EMPLOYEES E1
JOIN EMPLOYEES E2 ON E1.DEPT_ID = E2.DEPT_ID
WHERE E1.DEPT_ID = 10
ORDER BY E1.EMP_ID, E2.EMP_ID;

Result9 rows · 3 columns

PERSON_A	PERSON_B	DEPT_ID
Aisha	Aisha	10
Aisha	Marcus	10
Aisha	Daniel	10
Marcus	Aisha	10
Marcus	Marcus	10
Marcus	Daniel	10
Daniel	Aisha	10
Daniel	Marcus	10
Daniel	Daniel	10

Nine rows, out of three people. Read the grid and both problems are right there.

• Everybody is paired with themselves. Aisha and Aisha, Marcus and Marcus, Daniel and Daniel. Each of those rows satisfies E1.DEPT_ID = E2.DEPT_ID perfectly, because it is one row matched against itself.
• Every real pair appears twice. Aisha with Marcus, and then Marcus with Aisha. The join has no idea that those are the same pair. It found both orderings and returned both.

The arithmetic is exactly what a join does. Three rows on the left, three on the right, and every one matches every other because they all share a department. 3 × 3 = 9.

There was no error and no warning. This is a correct answer to the query we actually wrote.

Let's fix both problems with one operator

We add a second condition to the ON clause: E1.EMP_ID < E2.EMP_ID. Less than, not equals.

Query

SELECT E1.FIRST_NAME AS PERSON_A, E2.FIRST_NAME AS PERSON_B, E1.DEPT_ID
FROM EMPLOYEES E1
JOIN EMPLOYEES E2 ON E1.DEPT_ID = E2.DEPT_ID AND E1.EMP_ID < E2.EMP_ID
ORDER BY E1.DEPT_ID, E1.EMP_ID, E2.EMP_ID;

Result5 rows · 3 columns

PERSON_A	PERSON_B	DEPT_ID
Aisha	Marcus	10
Aisha	Daniel	10
Marcus	Daniel	10
Lena	Tomas	20
Priya	Sofia	30

Five rows, and this time we asked for every department rather than department 10 alone. Nobody is paired with themselves and no pair appears twice.

One operator did both jobs. Let's see why they are really the same job.

The row the join wanted to return	What < does to it
Aisha with Aisha, a row matched against itself	Dropped. 1 < 1 is false, so a row can never pair with itself.
Aisha with Marcus, the pair in ascending ID order	Kept. 1 < 2 is true.
Marcus with Aisha, the same pair reversed	Dropped. 2 < 1 is false, so exactly one of the two orderings survives.

Any strict comparison on a unique column would do it. <= would not. It keeps the self-pairs, because a value is always less than or equal to itself. <> would not either. It kills the self-pairs but keeps both orderings of every real pair. Strictly less than is the one that solves both at once.

Now let's read the grid by department. Three pairs in department 10, one in department 20, one in department 30. Grace is in none of them, and the setup script says why. Her DEPT_ID is NULL, and NULL = NULL is not true, so she pairs with nobody. Not even herself.

Why it belongs in ON

We put the < in the ON clause, not in a WHERE clause. On an inner join either place gives the same result, and ON is the better habit. The condition is part of what we mean by “a pair”, and if we later change the join to a LEFT JOIN, a condition left behind in WHERE will quietly undo it.

Now let's ask two questions we could not ask before

We have the employee and the manager side by side in one row now. That means comparisons between them are ordinary column comparisons.

First question. Does anybody earn more than their own manager?

Query

SELECT E.FIRST_NAME AS EMPLOYEE, E.SALARY AS EMP_SALARY,
       M.FIRST_NAME AS MANAGER, M.SALARY AS MGR_SALARY
FROM EMPLOYEES E
JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMP_ID
WHERE E.SALARY > M.SALARY
ORDER BY E.EMP_ID;

Result0 rows · 4 columns

EMPLOYEE	EMP_SALARY	MANAGER	MGR_SALARY

Column headings and nothing under them. That is an answer, not a failure. In this data nobody out-earns their manager, and an empty result is how SQL says so.

Zero rows is a result

An empty grid and a broken query look identical. Here is the habit that separates them. Check the query, then believe the result. Relax the condition to >=, or drop the WHERE clause and confirm rows come back at all. Once the query is sound, record the zero. Quietly assuming an empty result must be a bug is how real findings get thrown away.

Second question: how many direct reports does each manager have?

Same join, grouped by the manager's name.

Query

SELECT M.FIRST_NAME AS MANAGER, COUNT(*) AS DIRECT_REPORTS
FROM EMPLOYEES E
JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMP_ID
GROUP BY M.FIRST_NAME
ORDER BY DIRECT_REPORTS DESC, MANAGER;

Result3 rows · 2 columns

MANAGER	DIRECT_REPORTS
Daniel	5
Lena	1
Priya	1

Three managers. Now let's check those counts against the inner join we ran earlier, the one that gave us every employee next to their manager. 5 + 1 + 1 = 7, which is exactly the number of rows that join returned. Every employee who has a manager is counted once, under that manager.

That check is worth doing every time. It is the cheapest one available, it tells us the join did what we think, and it is the check that would have caught our nine-row pairs result before anyone presented it.

Group by the key, not the label

The GROUP BY is on M.FIRST_NAME, which works here only because the first names in this table happen to be unique. On real data, group by the manager's ID and carry the name along for display. Otherwise two managers with the same first name are silently merged into one row.

Can we go two levels deep?

Nothing stops us listing the table a third time. Let's join the employee to their manager, then join the manager to their manager.

Query

SELECT E.FIRST_NAME AS EMPLOYEE, M.FIRST_NAME AS MANAGER, G.FIRST_NAME AS GRAND_MANAGER
FROM EMPLOYEES E
LEFT JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMP_ID
LEFT JOIN EMPLOYEES G ON M.MANAGER_ID = G.EMP_ID
ORDER BY E.EMP_ID;

Result8 rows · 3 columns

EMPLOYEE	MANAGER	GRAND_MANAGER
Aisha	Daniel	NULL
Marcus	Daniel	NULL
Lena	Daniel	NULL
Tomas	Lena	Daniel
Priya	Daniel	NULL
Daniel	NULL	NULL
Sofia	Priya	Daniel
Grace	Daniel	NULL

Eight rows, and Tomas's row is the one to read. Tomas reports to Lena, and Lena reports to Daniel.

Both joins are LEFT joins. So a row that runs out of ancestors fills with NULL instead of disappearing.

And here is where the technique runs out. Each extra level of hierarchy costs us another copy of the table in the query. Three levels is three joins. Ten levels is ten. Worse than that, we have to know the depth before we can write the query, and a real org chart does not tell us its depth in advance.

So a self join is the right tool when the depth is fixed and small. One level for “who is my manager”, two for “and theirs”. For a tree of unknown depth, SQL has a recursive construct built for it, and that is Episode 36.

Key points

• A self join is not a join type. It is an ordinary join with the same table on both sides, and Snowflake treats the two sides as independent tables.
• The aliases are mandatory. Without them the table name becomes the alias and Snowflake refuses to compile with duplicate alias.
• Following a nullable pointer with an inner join drops the row whose pointer is NULL. In a hierarchy that is the root. LEFT JOIN keeps it.
• The second use of a self join is comparing rows against other rows of the same table, which has nothing to do with pointers.
• Joining on the shared column alone returns every row paired with itself and every real pair in both orders: three people in one department gave 9 rows.
• Adding E1.EMP_ID < E2.EMP_ID fixes both at once. Strictly less than, because <= keeps the self-pairs and <> keeps both orderings.
• A row whose join key is NULL pairs with nobody, not even itself. Grace is in none of the pairs.
• An empty result is an answer. Check the query, then believe the zero.
• Each extra level of hierarchy needs another copy of the table, and you must know the depth in advance. That is where this technique stops and recursion starts.

Next in this series

In the next post we will cover non-equi joins. The ON clause is a condition, not an equality, so we will join on BETWEEN and on greater-than, and we will look at the two ways a band join gives us the wrong row count without raising an error.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the setup script shown above, including the compilation error and the empty result. The EMPLOYEES data is invented demo data created by that script. This episode captures no SELECT * of EMPLOYEES, so the only row count claimed for the source table is the one visible in the three-column EMP_ID / FIRST_NAME / MANAGER_ID grid above. No performance claims are made anywhere in this series — nothing on this page was timed, so nothing about speed is said. Join behaviour is described from the Snowflake JOIN documentation.