Snowflake SQL Tutorial 36 - CONNECT BY in Snowflake

Snowflake SQL · Episode 36

In this post we are going to cover CONNECT BY in Snowflake.

CONNECT BY walks a hierarchy. We give it a row to start at and we tell it how to get from a row to that row's children, and it follows that step over and over until it runs out of rows. A self join can follow a pointer once. CONNECT BY follows it as many times as the data needs, and we never have to say how deep the tree is.

We will start by looking at the hierarchy the way it is actually stored. Then we will walk it downwards, indent it so it reads like an org chart, walk it upwards, print each row's full chain as text, check that the walk reached everybody, and finish with the same job written as a recursive CTE.

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

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

On this page

1. Let's look at the hierarchy as it is stored
2. Let's start the walk with START WITH and CONNECT BY
3. Now let's indent by LEVEL
4. Can we start the walk somewhere else?
5. Now let's walk the other way
6. Let's get the whole chain as text
7. Did the walk reach everybody?
8. Now let's count everybody below Daniel
9. Can we do the same thing without CONNECT BY?

Let's look at the hierarchy as it is stored

There is no tree in the database. There is one table with a column that points at another row of the same table, and that pointer is the whole hierarchy.

We are using EMPLOYEES, the table created back in Episode 1 and used by every episode since. It has a MANAGER_ID, and three of its columns are all we need today.

The hierarchy as it is stored

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. An employee ID, a name, and the ID of that person's manager. Daniel has no MANAGER_ID at all, which is what makes him the root. There is nobody above him. Everybody else points at somebody.

Read as a grid, that is a list of pointers. Read as a tree, it is an org chart. Nothing in the table marks which rows are deep and which are shallow. We have to work that out by following the pointers, and following pointers over and over is exactly what a plain SELECT cannot do.

Why not a self join

A self join can follow that pointer once. Episode 30 did precisely that, and it is the right tool when you know you need one level. Two levels means writing the join a second time, and three means a third. So the query has to be rewritten whenever the tree gets deeper, and it cannot be written at all if you do not know how deep the tree is.

Let's start the walk with START WITH and CONNECT BY

Two clauses and one keyword do the whole job.

Part	The question it answers
START WITH	Where does the walk begin?
CONNECT BY	How do I get from a row to its children?
PRIOR	Which of these two columns refers to the row I came from?

START WITH MANAGER_ID IS NULL picks the root row. CONNECT BY MANAGER_ID = PRIOR EMP_ID says how to step down one rung. A child is any row whose MANAGER_ID equals the EMP_ID of the row we arrived from. Snowflake then repeats that step until it runs out of children.

Let's run it.

Query

SELECT EMP_ID, FIRST_NAME, MANAGER_ID, LEVEL
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID = PRIOR EMP_ID
ORDER BY LEVEL, EMP_ID;

Result8 rows · 4 columns

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

Eight rows. Every employee, each one reached from the root by following the pointer as many times as it took. And the query never says how many times that is.

Where the depth comes from

The last column is the interesting one. LEVEL is not stored in EMPLOYEES and it is not stored anywhere else either. It is a pseudo-column that CONNECT BY makes available, and it holds the number of steps it took to reach that row. We select it like a column because inside a hierarchical query it behaves like one.

Read that column and the shape of the org chart is right there. Daniel on his own at LEVEL 1, then five people at LEVEL 2, then Tomas and Sofia at LEVEL 3. Tomas reports to Lena and Sofia reports to Priya, so both of them sit one rung further down than their colleagues.

The keyword that does the work

PRIOR is not optional decoration. Drop it and the condition becomes MANAGER_ID = EMP_ID, which is a test on a single row rather than a link between two rows. The walk cannot continue past where it started. PRIOR is the only thing in that clause that refers to a different row.

Now let's indent by LEVEL

LEVEL is a number, which means we can do arithmetic with it. Let's pad the name with four spaces for every rung below the root.

Query

SELECT LPAD(' ', (LEVEL - 1) * 4, ' ') || FIRST_NAME AS TREE, LEVEL, EMP_ID, MANAGER_ID
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID = PRIOR EMP_ID
ORDER BY LEVEL, EMP_ID;

Result8 rows · 4 columns

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

The same eight rows and the same traversal, with one function added. LPAD builds a string of (LEVEL - 1) * 4 spaces. Nothing at the root, four at the next rung, eight at the one below. Then || glues the name onto the end of it.

This is the one worth keeping. The same information was in the previous grid, but almost nobody reads a hierarchy out of a numeric column. Everybody reads one out of an indent.

The order is still ours to ask for

ORDER BY LEVEL, EMP_ID is doing some of the work here. An indent only looks like a tree if parents come before their children, and like everywhere else in SQL, CONNECT BY gives us a set and not an order. We asked for that ordering. The walk did not hand it to us.

Can we start the walk somewhere else?

Yes. START WITH takes any condition, not just MANAGER_ID IS NULL. Let's name a row by its ID and start the walk there.

Query

SELECT EMP_ID, FIRST_NAME, MANAGER_ID, LEVEL
FROM EMPLOYEES
START WITH EMP_ID = 6
CONNECT BY MANAGER_ID = PRIOR EMP_ID
ORDER BY LEVEL, EMP_ID;

Result8 rows · 4 columns

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

That is the same eight rows we got the first time, and it is worth being straight about why rather than glossing over it. Employee 6 is Daniel, the root. So asking to start at the root by ID and asking to start at the root by MANAGER_ID IS NULL are the same request written two ways. Nothing was narrowed.

The point still stands. START WITH sets the top of the walk. Point it at a row further down and the traversal only reaches that row's descendants, because CONNECT BY never walks back up past its own starting point.

Now let's walk the other way

Everything so far walked downwards, from a manager to the people who report to them. Let's move PRIOR onto the other column and nothing else.

Condition	Direction	Meaning
MANAGER_ID = PRIOR EMP_ID	Downwards	Find rows whose manager is the row I came from
PRIOR MANAGER_ID = EMP_ID	Upwards	Find the row that the row I came from reports to

Let's start at Sofia, one of the two rows at the bottom of the chart, and walk upwards.

Query

SELECT EMP_ID, FIRST_NAME, MANAGER_ID, LEVEL
FROM EMPLOYEES
START WITH EMP_ID = 7
CONNECT BY PRIOR MANAGER_ID = EMP_ID
ORDER BY LEVEL;

Result3 rows · 4 columns

EMP_ID	FIRST_NAME	MANAGER_ID	LEVEL
7	Sofia	5	1
5	Priya	6	2
6	Daniel	NULL	3

Three rows. Sofia, then Priya, then Daniel. That is a chain of command read from the bottom up, and it stops on its own when it reaches a row with no manager to follow.

Now look at what LEVEL means here. It is still the number of steps from the start of the walk, so Sofia is 1 and Daniel, the root of the org chart, is the deepest value in this result. LEVEL is distance travelled, not rank in the organisation. Same table and same keyword. PRIOR just moved.

Let's get the whole chain as text

The walk knows the route it took to reach each row, and SYS_CONNECT_BY_PATH hands it to us as a string. We give it a column and a separator and it joins that column up for every row on the way down.

Query

SELECT EMP_ID, FIRST_NAME, LEVEL,
       SYS_CONNECT_BY_PATH(FIRST_NAME, ' > ') AS PATH
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID = PRIOR EMP_ID
ORDER BY PATH;

Result8 rows · 4 columns

EMP_ID	FIRST_NAME	LEVEL	PATH
6	Daniel	1	> Daniel
1	Aisha	2	> Daniel > Aisha
8	Grace	2	> Daniel > Grace
3	Lena	2	> Daniel > Lena
4	Tomas	3	> Daniel > Lena > Tomas
2	Marcus	2	> Daniel > Marcus
5	Priya	2	> Daniel > Priya
7	Sofia	3	> Daniel > Priya > Sofia

Now each row carries its own ancestry. Tomas is not just at LEVEL 3. The result tells us which rows he is below, and in what order.

Worth knowing before you use it

Two details the grid shows and the syntax does not. The separator is written before every element, including the root, so each path begins with the separator rather than with a name. Trim it if you are displaying these. And because the path is text, ORDER BY PATH sorts the tree into reading order: a parent, then that parent's subtree, alphabetically. That is why Tomas appears directly beneath Lena here rather than with the other deepest row.

Did the walk reach everybody?

A traversal only returns rows it could reach. A row whose MANAGER_ID points at an employee who no longer exists is unreachable from the root, so it is silently absent from the result. No error, no warning, just a shorter grid than we expected. That is worth a check rather than an assumption.

So let's take every employee, subtract the ones the traversal returned, and look at what is left. MINUS is from the previous post.

Query — every employee, minus the traversal

SELECT EMP_ID, FIRST_NAME, MANAGER_ID FROM EMPLOYEES
MINUS
SELECT EMP_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID = PRIOR EMP_ID;

Result0 rows · 3 columns

EMP_ID	FIRST_NAME	MANAGER_ID

Empty. Nobody was missed. Every row in EMPLOYEES is reachable from the root, so our tree really is one connected tree and not a tree plus some orphans. On a real hierarchy this is the query to run before trusting any count that came out of a traversal.

Read this one backwards

An empty grid is the good answer here, which makes this check easy to misread. Rows coming back from it are not a bonus. They are employees your hierarchy report is leaving out.

Now let's count everybody below Daniel

How many people are below Daniel, at any depth? Not his direct reports. Everybody, however many rungs down they sit.

Let's count the traversal and subtract the starting row, which is Daniel himself.

Query

SELECT COUNT(*) - 1 AS PEOPLE_BELOW_DANIEL
FROM (
  SELECT EMP_ID
  FROM EMPLOYEES
  START WITH EMP_ID = 6
  CONNECT BY MANAGER_ID = PRIOR EMP_ID
);

Result1 row · 1 column

PEOPLE_BELOW_DANIEL
7

Seven. That is the whole organisation under the root, counted without anybody having to state how deep it goes. The same query answers it on a chart of four levels or forty.

A single self join cannot answer this at all. It can count direct reports, because that is one hop. It cannot count descendants, because it does not know how many hops there are, and the number of joins we would have to write depends on the answer.

Can we do the same thing without CONNECT BY?

We can. A recursive common table expression does the same job. An anchor query that finds the root, then a query that joins the table back onto the results so far, repeated until nothing new comes out.

Query

WITH RECURSIVE TREE (EMP_ID, FIRST_NAME, MANAGER_ID, LVL) AS (
  SELECT EMP_ID, FIRST_NAME, MANAGER_ID, 1 FROM EMPLOYEES WHERE MANAGER_ID IS NULL
  UNION ALL
  SELECT E.EMP_ID, E.FIRST_NAME, E.MANAGER_ID, T.LVL + 1
  FROM EMPLOYEES E JOIN TREE T ON E.MANAGER_ID = T.EMP_ID
)
SELECT EMP_ID, FIRST_NAME, MANAGER_ID, LVL FROM TREE ORDER BY LVL, EMP_ID;

Result8 rows · 4 columns

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

The same eight rows with the same depths. Here the depth is in a column called LVL, because this version has to work the depth out for itself rather than being handed it. That is the T.LVL + 1 in the recursive half. Start the anchor row at 1 and add one at every step.

	CONNECT BY	Recursive CTE
Amount of SQL	Less. Two clauses	More. Two queries and a join
Depth	LEVEL, provided for you	Computed by hand in the recursive half
Path	SYS_CONNECT_BY_PATH	Concatenate it yourself, the same way as the depth
Portability	Snowflake and Oracle. Not standard SQL	Standard SQL. It ports

No performance claim

Nothing on this page was timed, so there is no claim here about which of the two is faster. Pick on readability, and on whether the query has to run anywhere else.

Recursive CTEs get a full post of their own in Level 4, where the WITH clause is the subject rather than a footnote.

That also closes Level 3. Thirteen posts, from the inner join all the way to walking a whole hierarchy with two clauses.

Key points

• A hierarchy is stored flat. One column pointing at another row of the same table. CONNECT BY follows that pointer over and over, as many times as the data requires.
• START WITH names the row the walk begins at. CONNECT BY says how to get from a row to the next one.
• PRIOR marks the column that refers to the row you came from. It is the only part of the condition that spans two rows.
• Which side PRIOR sits on decides the direction. MANAGER_ID = PRIOR EMP_ID walks down, PRIOR MANAGER_ID = EMP_ID walks up.
• LEVEL is a pseudo-column holding the number of steps from the start of the walk. It is stored in no table, and it is distance travelled rather than rank. Walking upwards makes the root the deepest value.
• LPAD(' ', (LEVEL - 1) * 4, ' ') || FIRST_NAME turns the grid into a readable tree, and it needs an ORDER BY to stay readable.
• SYS_CONNECT_BY_PATH gives each row its full chain from the root as text, with the separator written in front of every element including the first.
• A traversal silently leaves out rows it cannot reach. Subtract it from the table with MINUS and confirm the result is empty.
• A recursive CTE does the same job in standard SQL, computing its own depth. CONNECT BY is shorter. The CTE ports.

Next in this series

In the next post Level 4 opens with scalar subqueries. A query that returns one row and one column is a value, and a value can stand wherever a value can stand, including inside a WHERE clause. It has two ways of going wrong, and only one of them tells you.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode. The EMPLOYEES data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Clause descriptions follow the Snowflake CONNECT BY documentation.