Snowflake SQL Tutorial 41 - Correlated Subqueries in Snowflake

Snowflake SQL · Episode 41

In this post we are going to cover correlated subqueries in Snowflake.

A subquery is correlated when it mentions a column from the outer query. That one reference changes everything about it. It can no longer be run on its own, and instead of one answer for the whole statement it gives us one answer per outer row.

We will start with a subquery that knows nothing about the outer query, then add a single reference and watch the question change. After that we will put a correlated subquery in the select list so we can read its answer changing down a column, look at what happens to a row whose correlation column is NULL, and delete one alias to see the correlation disappear without an error. We finish on the shape that is genuinely awkward any other way.

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

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

On this page

1. Let's create some sample data
2. Let's start with a subquery that knows nothing about the outer query
3. Now let's watch it happen in the SELECT list
4. Now let's look at Grace's row: NULL and 0 at the same time
5. What happens if we forget the alias?
6. Can a plain join do the same job? Often it can
7. Now let's find where correlation earns its place
8. One last thing: we have been writing these already

Let's create some sample data

Run the script below once if you do not already have these tables. The column to read is DEPT_ID, because that is the one we are going to correlate on.

Sample data

-- The tables already exist if you have followed this course from the start.
-- EMPLOYEES carries the column this episode correlates on - DEPT_ID - and one
-- employee whose DEPT_ID is NULL. CUSTOMERS and ORDERS come back in the last
-- three sections.
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,            -- <<-- the correlation column
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

-- Grace, on the last line, has no department. Her row gets a section of its own.
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');

-- CUSTOMERS and ORDERS are the shared sample tables for this course. ORDERS has
-- one row per order with a CUSTOMER_ID, an ORDER_DATE and an AMOUNT.

This whole post turns on the difference between one average and several, so let's get both on the page before we write any subquery at all.

First, one number for the whole company.

Query

SELECT AVG(SALARY) AS COMPANY_AVG FROM EMPLOYEES;

Result1 row · 1 column

COMPANY_AVG
99581.25000000

Now the same calculation per department.

Query

SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEES
GROUP BY DEPT_ID
ORDER BY DEPT_ID NULLS LAST;

Result4 rows · 3 columns

DEPT_ID	HEADCOUNT	AVG_SALARY
10	3	115166.66666667
20	2	95625.00000000
30	2	83950.00000000
NULL	1	92000.00000000

Four rows, and the last one is the interesting one. GROUP BY DEPT_ID has given NULL its own group. One employee, with an average of 92000.00000000, who is not in any department.

Remember the NULL group

Hold on to that row. Further down we ask the same question with a correlated subquery instead of a GROUP BY, and the same employee gets a completely different kind of answer. Two clauses, one column, two rules about NULL.

Let's start with a subquery that knows nothing about the outer query

Who earns more than average? We answered that in Episode 37 with a subquery that works out one number for the whole table. It can be run on its own, it knows nothing about the outer query, and it is worked out once.

Uncorrelated — above the company average

SELECT E.EMP_ID, E.FIRST_NAME, E.DEPT_ID, E.SALARY
FROM EMPLOYEES E
WHERE E.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES)
ORDER BY E.DEPT_ID, E.SALARY DESC;

Result3 rows · 4 columns

EMP_ID	FIRST_NAME	DEPT_ID	SALARY
6	Daniel	10	131000.00
1	Aisha	10	118000.00
3	Lena	20	104000.00

Three rows. Daniel, Aisha and Lena. Each of their salaries beats the 99581.25000000 company average we worked out above.

Now let's run the same statement with one clause added inside the brackets. The clause is WHERE D.DEPT_ID = E.DEPT_ID, and E is the outer query's alias.

Correlated — above their own department's average

SELECT E.EMP_ID, E.FIRST_NAME, E.DEPT_ID, E.SALARY
FROM EMPLOYEES E
WHERE E.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES D
                   WHERE D.DEPT_ID = E.DEPT_ID)
ORDER BY E.DEPT_ID, E.SALARY DESC;

Result4 rows · 4 columns

EMP_ID	FIRST_NAME	DEPT_ID	SALARY
6	Daniel	10	131000.00
1	Aisha	10	118000.00
3	Lena	20	104000.00
5	Priya	30	96500.00

Four rows. The newcomer is Priya, and she is worth following, because she explains the whole thing.

• Priya earns 96500.00, which is below the company average of 99581.25000000, so she failed the first query.
• She is in department 30, whose average is 83950.00000000, so she passes the second one comfortably.

Same table, same operator, same comparison. One reference to the outer query, and it is a different question. Above average became above their own department's average, which is almost always what somebody actually meant.

Why that one reference matters so much

	Uncorrelated	Correlated
Mentions the outer query?	No	Yes — E.DEPT_ID
Can be run on its own?	Yes	No — E is not defined outside the statement
How many times is it evaluated?	Once for the statement	Once per outer row, logically
Answer it produces	The same number for every row	A different number per row

Logically, not literally

“Once per outer row” is the meaning of the query, not a description of how Snowflake runs it. The optimiser is free to rewrite a correlated subquery into a join or an aggregate as long as the answer is the same, and this series makes no claims about execution or timing. What is guaranteed is the result: one answer per outer row.

Now let's watch it happen in the SELECT list

A correlated subquery is not stuck in the WHERE clause. We can put one in the select list and its per-row answer becomes a column we can read. That is the clearest way to see what once per row means.

Let's ask for each employee, their department's average, and their department's headcount. Two correlated subqueries, both correlated on the same column.

Query

SELECT E.FIRST_NAME,
       E.DEPT_ID,
       E.SALARY,
       (SELECT AVG(SALARY) FROM EMPLOYEES D WHERE D.DEPT_ID = E.DEPT_ID) AS DEPT_AVG,
       (SELECT COUNT(*)    FROM EMPLOYEES D WHERE D.DEPT_ID = E.DEPT_ID) AS DEPT_HEADCOUNT
FROM EMPLOYEES E
ORDER BY E.DEPT_ID NULLS LAST, E.SALARY DESC;

Result8 rows · 5 columns

FIRST_NAME	DEPT_ID	SALARY	DEPT_AVG	DEPT_HEADCOUNT
Daniel	10	131000.00	115166.66666667	3
Aisha	10	118000.00	115166.66666667	3
Marcus	10	96500.00	115166.66666667	3
Lena	20	104000.00	95625.00000000	2
Tomas	20	87250.00	95625.00000000	2
Priya	30	96500.00	83950.00000000	2
Sofia	30	71400.00	83950.00000000	2
Grace	NULL	92000.00	NULL	0

Eight rows, and the DEPT_AVG column changes as we go down it. 115166.66666667 for everybody in department 10, 95625.00000000 for department 20, 83950.00000000 for department 30. Those are exactly the values the GROUP BY grid gave us at the top of this page, arrived at from the other direction.

In Episode 37, the equivalent column held the same number on every row, because the subquery ran once. This one varies, because it answered the question again for each row. That is the whole difference between the two posts, visible in one column.

Now let's look at Grace's row: NULL and 0 at the same time

Look at the last row of that select-list grid. Here is the same employee on her own.

Query

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

Result1 row · 4 columns

EMP_ID	FIRST_NAME	DEPT_ID	SALARY
8	Grace	NULL	92000.00

Grace's DEPT_ID is NULL. So inside both subqueries, D.DEPT_ID = E.DEPT_ID compares something to NULL, which is never TRUE. Both of her subqueries matched no rows at all.

Same situation, and two different-looking answers back in the select-list grid. DEPT_AVG came back NULL and DEPT_HEADCOUNT came back 0.

Subquery	Rows it matched	What it returned	Why
COUNT(*)	none	0	Counting nothing is a perfectly good number
AVG(SALARY)	none	NULL	There is nothing to divide by — no value exists to report

Both are correct, and the reason they both produce something at all is worth saying plainly. An aggregate with no GROUP BY always returns exactly one row, even over an empty input. So a scalar subquery like this never returns nothing. It returns one value, and that value may be NULL.

Now let's compare that with the GROUP BY answer

The GROUP BY grid gave this same employee her own row, with a headcount and an average of 92000.00000000. The correlated subquery gave her NULL and 0. Same data, same column, and the two clauses disagree completely about what a NULL is.

Clause	Treatment of NULL	Result for the employee with no department
GROUP BY DEPT_ID	NULL is a group like any other value	Her own row, with her own average
WHERE D.DEPT_ID = E.DEPT_ID	= is never true for NULL	No matching rows, so NULL and 0

Two clauses, two rules

This pair is the thing to remember from the post after the alias mistake we are about to make. GROUP BY groups NULLs together, and = matches none of them. If a correlated query and a GROUP BY query disagree about a total, a nullable correlation column is the first place to look.

What happens if we forget the alias?

This is the mistake that costs an afternoon, and it is one keystroke.

Let's take the alias off the inner table. The correlation clause now reads WHERE DEPT_ID = DEPT_ID. The inner table is the only table in scope inside the subquery, so both of those names resolve to the inner table. It is a column compared to itself.

A column compared to itself is true for every row that has a value, so the clause filters nothing and the correlation is gone. What it is not, is an error.

Without the alias on the inner table

SELECT COUNT(*) AS N
FROM EMPLOYEES E
WHERE E.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPT_ID = DEPT_ID);

Result1 row · 1 column

N
3

Now let's run the same count with the alias put back and nothing else changed.

With the alias — genuinely correlated

SELECT COUNT(*) AS N
FROM EMPLOYEES E
WHERE E.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES D WHERE D.DEPT_ID = E.DEPT_ID);

Result1 row · 1 column

N
4

The broken version has stopped asking about each employee's own department entirely. With the correlation gone, the subquery works out one average and compares every employee against it. It is an uncorrelated query again, and the count it gives us back is the same count the uncorrelated query we started with gave us.

A detail the matching counts hide

It is not quite the same average, which is worth knowing before you reason from the count. DEPT_ID = DEPT_ID is unknown rather than true for the employee whose department is NULL, so her salary is left out of the average the broken subquery works out. The important point is the one the two grids show: it is a single number for the whole statement instead of one per row.

Two queries that look almost identical, both valid, both running without complaint, answering different questions.

The habit that prevents it

Always alias the inner table in a correlated subquery, and always qualify both sides of the correlation clause. There is no error, no warning and no NULL in the output to hint at it. The only symptom is a number that is wrong in a way that looks entirely plausible.

Can a plain join do the same job? Often it can

Correlated subqueries are not automatically the right tool. Let's ask for orders per customer, written with two of them. One for the count, one for the sum.

Two correlated subqueries

SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       (SELECT COUNT(*)    FROM ORDERS O WHERE O.CUSTOMER_ID = C.CUSTOMER_ID) AS N_ORDERS,
       (SELECT SUM(AMOUNT) FROM ORDERS O WHERE O.CUSTOMER_ID = C.CUSTOMER_ID) AS TOTAL
FROM CUSTOMERS C
ORDER BY C.CUSTOMER_ID;

Result10 rows · 4 columns

CUSTOMER_ID	CUSTOMER_NAME	N_ORDERS	TOTAL
101	Northwind Traders	9	61660.00
102	Blue Ridge Supply	6	6060.50
103	Kestrel Analytics	5	15635.25
104	Harbour Foods	4	9076.25
105	Alpine Logistics	5	36565.00
106	Delta Print Co	4	3760.00
107	Meridian Health	5	56800.00
108	Copper Kettle Ltd	0	NULL
109	Vantage Media	3	15870.00
110	Sable Interiors	0	NULL

Ten rows, one per customer. Two customers have no orders, so their count is 0 and their total is NULL. That is the same thing we saw on Grace's row, for the same reason. COUNT over no rows is a number, and SUM over no rows has no value to report.

Now let's ask the identical question with a LEFT JOIN and a GROUP BY.

LEFT JOIN and GROUP BY

SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       COUNT(O.ORDER_ID) AS N_ORDERS,
       SUM(O.AMOUNT)     AS TOTAL
FROM CUSTOMERS C
LEFT JOIN ORDERS O ON O.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME
ORDER BY C.CUSTOMER_ID;

Result10 rows · 4 columns

CUSTOMER_ID	CUSTOMER_NAME	N_ORDERS	TOTAL
101	Northwind Traders	9	61660.00
102	Blue Ridge Supply	6	6060.50
103	Kestrel Analytics	5	15635.25
104	Harbour Foods	4	9076.25
105	Alpine Logistics	5	36565.00
106	Delta Print Co	4	3760.00
107	Meridian Health	5	56800.00
108	Copper Kettle Ltd	0	NULL
109	Vantage Media	3	15870.00
110	Sable Interiors	0	NULL

The same ten rows, cell for cell. We are not claiming either one is faster. Nothing on this page was timed and this series makes no performance claims. But the join is a shape we already know and it reads just as clearly, so when a plain join answers the question, use the join.

One difference between the two queries worth noticing

The correlated version counts COUNT(*). The join version has to count a column, COUNT(O.ORDER_ID), because after a LEFT JOIN a customer with no orders still produces one row with NULLs on the order side. COUNT(*) would count that row and report 1 order. Counting a column from the right-hand table ignores the NULL and reports none. The correlated version never faces that problem, because there is no padded row to miscount.

Make it reconcile

Here is a free check on a per-row subquery. The N_ORDERS column adds up to 41, which is the total number of rows in ORDERS as recorded in this course's captured source-table counts. Every order belongs to exactly one customer, so if that sum came out short or long, the correlation would be wrong. Arithmetic on one column, and it costs nothing.

Now let's find where correlation earns its place

Here is the shape that is genuinely awkward without a correlated subquery. Each customer's largest order, and not just the amount but the whole row, with its ORDER_ID and its date.

An aggregate on its own cannot do this. MAX(AMOUNT) ... GROUP BY CUSTOMER_ID gives us the amount and throws away which order it was. So let's do it the other way. Keep every order whose amount equals the largest amount that customer has, which is a question about the outer row.

Query

SELECT O.ORDER_ID, O.CUSTOMER_ID, O.ORDER_DATE, O.AMOUNT
FROM ORDERS O
WHERE O.AMOUNT = (SELECT MAX(O2.AMOUNT) FROM ORDERS O2
                   WHERE O2.CUSTOMER_ID = O.CUSTOMER_ID)
ORDER BY O.CUSTOMER_ID;

Result8 rows · 4 columns

ORDER_ID	CUSTOMER_ID	ORDER_DATE	AMOUNT
1040	101	2024-10-16	9640.00
1028	102	2024-03-21	1240.00
1031	103	2024-05-10	4025.00
1036	104	2024-08-02	2540.00
1026	105	2024-02-17	8340.00
1027	106	2024-03-04	1690.00
1037	107	2024-08-25	13100.00
1038	109	2024-09-11	5930.00

Eight rows, and the CUSTOMER_ID column goes up without repeating. One order per customer. The two customers with no orders are simply absent, which is correct. A customer with nothing to compare has no largest order.

The caveat this data does not show

The = (SELECT MAX(...)) pattern returns every row tied for the maximum, not one arbitrary row. This data happens to contain no tie, which is why each customer appears once here. On data where two orders share a customer's highest amount, both come back, and a downstream join that assumed one row per customer starts duplicating.

Snowflake has a neater answer for this exact shape later in the course. A window function with ROW_NUMBER() ranks the orders within each customer and lets us keep the first one, tie or no tie. That is Level 5. Until then, the correlated form is the one that works and reads correctly.

One last thing: we have been writing these already

One loop to close. Every EXISTS query in Episode 39 looked like this.

Query — customers with at least one large order

SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME
FROM CUSTOMERS C
WHERE EXISTS (SELECT 1 FROM ORDERS O
               WHERE O.CUSTOMER_ID = C.CUSTOMER_ID AND O.AMOUNT > 7000)
ORDER BY C.CUSTOMER_ID;

Result3 rows · 2 columns

CUSTOMER_ID	CUSTOMER_NAME
101	Northwind Traders
105	Alpine Logistics
107	Meridian Health

That subquery mentions C.CUSTOMER_ID from the outer query, so it is a correlated subquery. Three rows, the customers with at least one order above 7000.

This is worth saying out loud, because it makes correlation ordinary rather than exotic. A bare EXISTS (SELECT 1 FROM ORDERS) with no correlation would ask whether the orders table has any rows at all, and would give us back every customer or none. The correlation is what turns it into a question about each row, and that is the only reason EXISTS is useful.

• A correlated subquery in WHERE filters the outer rows, one decision per row.
• A correlated subquery in the select list produces a value, one per row — and it must return a single column and at most one row.
• EXISTS and NOT EXISTS are correlated in essentially every useful case.
• If the subquery mentions nothing from outside itself, it is not correlated, and it will give you the same answer on every row.

Key points

• A subquery is correlated when it references a column from the outer query. That makes it a question about each outer row rather than about the table.
• Correlated or not changes the meaning of the query, not just its shape: above the company average and above your own department's average return different people from the same data.
• Put a correlated subquery in the select list and you can watch the answer change down the column. That is the whole concept in one grid.
• A scalar aggregate subquery always returns exactly one value, even when it matched nothing: COUNT gives 0, AVG and SUM give NULL.
• GROUP BY gives NULL its own group; = never matches a NULL. The same employee gets an average from one and NULL from the other.
• Leave the alias off the inner table and DEPT_ID = DEPT_ID becomes a column compared to itself. The correlation vanishes, the query runs, and what you get back is quietly an uncorrelated answer.
• Always alias the inner table and qualify both sides of the correlation clause. There is no error to catch this for you.
• When a LEFT JOIN and GROUP BY answers the question, prefer it — it is a familiar shape and it returned identical rows here.
• Correlation earns its place when you need the whole row that an aggregate identifies, such as each customer's largest order — remembering that an equals-the-maximum test returns every tied row.

Next in this series

In the next post we will cover derived tables. A subquery in the FROM clause, used as though it were a table: how to alias it, what it can and cannot see, and why it is the step before common table expressions.

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, including both halves of the alias mistake. The EMPLOYEES, CUSTOMERS and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series: nothing here was timed, and “once per outer row” describes what a correlated subquery means, not how Snowflake chooses to execute it. Subquery behaviour follows the Snowflake subquery documentation.