Snowflake SQL Tutorial 6 - NULL in Snowflake Explained - Why NOT IN Can Return ZERO Rows

Snowflake SQL · Episode 6

In this post we are going to cover NULL in Snowflake.

NULL is not a value. It is a marker that says there is no value here. Either nobody knows it, or it does not apply. Every strange thing NULL does follows from that one idea.

We will start with the empty cells in our own table and work up to the query that does real damage. By the end we will know why NOT IN against the wrong subquery returns nothing at all, with no error to tell us. We will also find out why one row has been going missing from our results for three posts in a row.

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

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

On this page

1. Let's create some sample data
2. Let's be clear what NULL is not
3. Let's ask Snowflake what NULL = NULL gives us
4. Now let's see what WHERE DEPT_ID = NULL returns
5. Let's use IS NULL and IS NOT NULL instead
6. Now let's watch NULL spread through a calculation
7. Let's stop the spread with COALESCE
8. Now let's count the rows two different ways
9. Now let's look at the NOT IN trap
10. One last thing: let's see where the NULLs sort

Let's create some sample data

Everything here runs against the same EMPLOYEES table the series has used from the start. The script below creates it and fills it with eight rows.

Copy this into a Snowflake worksheet and run it once. Then you can run every query on this page yourself.

Sample data

-- Creates the EMPLOYEES table used by every episode in this series.
-- Run it once; every later episode can reuse it.
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');

Three of those columns are allowed to hold nothing, and the whole post happens inside them. Let's put them next to a name so we can see exactly which cells are empty.

Query

SELECT FIRST_NAME, DEPT_ID, COMMISSION_PCT, MANAGER_ID FROM EMPLOYEES;

Result8 rows · 4 columns

FIRST_NAME	DEPT_ID	COMMISSION_PCT	MANAGER_ID
Aisha	10	NULL	6
Marcus	10	NULL	6
Lena	20	0.05	6
Tomas	20	NULL	3
Priya	30	0.10	6
Daniel	10	NULL	NULL
Sofia	30	0.08	5
Grace	NULL	NULL	6

Read down the columns rather than across the rows. Grace has no DEPT_ID. Daniel has no MANAGER_ID, because he is at the top of the reporting tree. And five of the eight have no COMMISSION_PCT. Only Lena on 0.05, Priya on 0.10 and Sofia on 0.08 earn commission at all.

Those cells are not blank in a cosmetic sense. They are the subject of this post.

Let's be clear what NULL is not

Look at Daniel's MANAGER_ID again. It is not 0. Zero would be a manager, one whose ID happens to be zero. Daniel genuinely has no manager, and NULL is how the table says so.

That difference is the whole reason NULL behaves the way it does for the rest of this page. Here are the three things people mix up, side by side.

Stored as	Means	Arithmetic on it	Equality test
0	a known value that happens to be zero	behaves normally	= 0 is TRUE
''	a known string with no characters in it	behaves normally	= '' is TRUE
NULL	no value at all — unknown, or not applicable	produces NULL	= NULL is NULL, never TRUE

That last cell is where most NULL bugs are born. So let's ask Snowflake to prove it.

Let's ask Snowflake what NULL = NULL gives us

We do not need a table for this one. We can ask Snowflake directly what a comparison against NULL evaluates to.

Query

SELECT NULL = NULL AS NULL_EQ_NULL, NULL != NULL AS NULL_NEQ_NULL, NULL IS NULL AS NULL_IS_NULL;

Result1 row · 3 columns

NULL_EQ_NULL	NULL_NEQ_NULL	NULL_IS_NULL
NULL	NULL	TRUE

NULL = NULL is not TRUE. It is NULL. NULL != NULL is NULL as well, so we cannot even get a FALSE out of it by flipping the operator. Only the third column, NULL IS NULL, comes back TRUE.

The documentation states it without hedging: “In most contexts, the Boolean expression NULL = NULL returns NULL, not TRUE.”

So SQL has three truth values, not two

The third one is what we have just seen. Here is what WHERE does with each of them.

Result of a comparison	When	What WHERE does with it
TRUE	the condition holds	keeps the row
FALSE	the condition does not hold	drops the row
NULL (unknown)	a NULL took part in the comparison	drops the row — it only keeps TRUE

The docs again, on that last row: “In a WHERE clause, if an expression evaluates to NULL, the row for that expression is removed from the result set.”

WHERE is not a test for “not false”. It is a test for TRUE.

Now let's see what WHERE DEPT_ID = NULL returns

Put those two facts together and this next query cannot work. It looks entirely reasonable, and everybody writes it once.

Query

SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID = NULL;

Result0 rows · 2 columns

FIRST_NAME	DEPT_ID

Zero rows. Not even Grace, whose DEPT_ID genuinely is NULL. Her comparison evaluated to NULL like everybody else's, and WHERE keeps only TRUE.

Watch out

This is the failure shape to watch for all the way through this page: no error, no warning, an empty or short result that looks like an answer. A syntax error costs you thirty seconds. A silently wrong row count can sit in a report for a year.

Let's use IS NULL and IS NOT NULL instead

The documentation tells us what to use instead in six words: use “IS [ NOT ] NULL to compare NULL values”. IS NULL is not an equality test, which is exactly why it survives contact with a NULL.

Let's ask for the employee with no department again, this time with IS NULL.

Query

SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID IS NULL;

Result1 row · 2 columns

FIRST_NAME	DEPT_ID
Grace	NULL

There she is. One row, the row the previous query could not reach.

And the negation does the matching job. Let's ask for everybody who does have a commission percentage.

Query

SELECT FIRST_NAME, COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL;

Result3 rows · 2 columns

FIRST_NAME	COMMISSION_PCT
Lena	0.05
Priya	0.10
Sofia	0.08

Three rows, Lena and Priya and Sofia. They are the only employees with a commission percentage on file.

Notice what this does not do. It does not return the other five with a zero in them. They are left out, because having no value is not the same as having the value zero.

The rule

IS NULL and IS NOT NULL are the only reliable way to test for a NULL in Snowflake. If you find yourself writing = NULL or <> NULL, the query will run and it will be wrong.

Now let's watch NULL spread through a calculation

So far NULL has only cost us rows in a WHERE clause. Now it starts getting into our numbers.

Let's work out everybody's commission, which is the salary multiplied by the commission percentage.

Query

SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY * COMMISSION_PCT AS COMMISSION FROM EMPLOYEES;

Result8 rows · 4 columns

FIRST_NAME	SALARY	COMMISSION_PCT	COMMISSION
Aisha	118000.00	NULL	NULL
Marcus	96500.00	NULL	NULL
Lena	104000.00	0.05	5200.0000
Tomas	87250.00	NULL	NULL
Priya	96500.00	0.10	9650.0000
Daniel	131000.00	NULL	NULL
Sofia	71400.00	0.08	5712.0000
Grace	92000.00	NULL	NULL

Three people get a number. Lena on 5200.0000, Priya on 9650.0000 and Sofia on 5712.0000. The other five get NULL, because anything multiplied by NULL is NULL. Aisha's 118000.00 salary is perfectly well known, and it buys her nothing here.

If we were summing that column to budget for commission, five of our eight employees would contribute nothing and Snowflake would not mention it.

Now let's try the same thing on text

Concatenation is not arithmetic, so we might expect it to be gentler. Let's join a first name to a department ID with a dash between them and see.

Query

SELECT FIRST_NAME, DEPT_ID, FIRST_NAME || '-' || DEPT_ID AS JOINED FROM EMPLOYEES;

Result8 rows · 3 columns

FIRST_NAME	DEPT_ID	JOINED
Aisha	10	Aisha-10
Marcus	10	Marcus-10
Lena	20	Lena-20
Tomas	20	Tomas-20
Priya	30	Priya-30
Daniel	10	Daniel-10
Sofia	30	Sofia-30
Grace	NULL	NULL

Seven labels come out exactly as we intended. Then look at Grace's. It is not Grace-NULL. It is not Grace-. The whole string is NULL, because one piece of it was.

Watch out

If you build keys, labels or display names by concatenating columns, a single nullable column in the expression can wipe out the whole value — including the parts that were present. This is a common way for a join key to go missing.

Let's stop the spread with COALESCE

COALESCE takes a list of expressions and returns the first one that is not NULL. We give it the column and then a fallback, and the NULL stops there.

Query

SELECT FIRST_NAME, COMMISSION_PCT, COALESCE(COMMISSION_PCT, 0) AS FIXED FROM EMPLOYEES;

Result8 rows · 3 columns

FIRST_NAME	COMMISSION_PCT	FIXED
Aisha	NULL	0.00
Marcus	NULL	0.00
Lena	0.05	0.05
Tomas	NULL	0.00
Priya	0.10	0.10
Daniel	NULL	0.00
Sofia	0.08	0.08
Grace	NULL	0.00

Where there is a percentage we get the percentage. Where there is not, we get a number instead of a hole. And that number is safe to multiply, sum and average.

Why it says 0.00 and not 0

Look closely at the fallback: you wrote 0 and the result reads 0.00. COALESCE returns a single type for the whole column, so the literal takes the scale of COMMISSION_PCT. Useful to know before you compare the output to a hand-typed expectation.

COALESCE has a whole family. NVL, NVL2, IFNULL, NULLIF and ZEROIFNULL all get an episode of their own later in the series. For now, this is the one to reach for.

Do not default blindly

Substituting a zero changes the answer to some questions and not others. “What is our total commission bill?” is unaffected. “What is the average commission percentage?” is changed completely, because five employees who had no percentage now drag the average down with a zero. Decide which question you are answering before you default a NULL away.

Now let's count the rows two different ways

This is the one that turns up in finished reports. Let's run three counts over the same eight rows of the same table.

Query

SELECT COUNT(*) AS COUNT_STAR, COUNT(COMMISSION_PCT) AS COUNT_COMMISSION, COUNT(DEPT_ID) AS COUNT_DEPT FROM EMPLOYEES;

Result1 row · 3 columns

COUNT_STAR	COUNT_COMMISSION	COUNT_DEPT
8	3	7

Three different numbers from one table. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL.

Neither one is wrong. They are answers to different questions, and the difference between them is exactly the number of NULLs in the column.

Now let's do the same thing on the ORDERS table

Here is the same pair of counts against the course's ORDERS table, with the sum alongside.

Query

SELECT COUNT(*) AS COUNT_STAR, COUNT(AMOUNT) AS COUNT_AMOUNT, SUM(AMOUNT) AS SUM_AMOUNT FROM ORDERS;

Result1 row · 3 columns

COUNT_STAR	COUNT_AMOUNT	SUM_AMOUNT
41	40	205427.00

COUNT(*) is 41. COUNT(AMOUNT) is 40. One order has no amount recorded. So SUM(AMOUNT), which comes back as 205427.00, is the total of 40 amounts and not 41.

The quiet wrong answer

Ask for the average order value and divide SUM(AMOUNT) by COUNT(*), and you divide a total of 40 orders by 41. The average comes out low, the query succeeds, and nothing anywhere tells you. AVG(AMOUNT) does the right thing — it ignores the NULL and divides by 40.

Now let's look at the NOT IN trap

This is the one the last three posts have been building towards, and it is the most damaging thing on the page.

The request is ordinary. Give us the employees whose department is not in this list. The list comes from a subquery, and because of how that subquery was written it happens to return a NULL among its values.

Query

SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID NOT IN (SELECT DEPT_ID FROM EMPLOYEES WHERE DEPT_ID IS NULL OR DEPT_ID = 40);

Result0 rows · 2 columns

FIRST_NAME	DEPT_ID

Zero rows. Not fewer rows than we expected. Nothing at all, from a table with eight rows in it, with no error and no warning.

So why can it not return anything?

NOT IN is shorthand for a chain of <> tests joined by AND. Is this value different from the first item, and different from the second, and…

NOT IN, unpacked

-- What you wrote
WHERE DEPT_ID NOT IN (10, NULL)

-- What it means
WHERE DEPT_ID <> 10 AND DEPT_ID <> NULL

-- And DEPT_ID <> NULL is NULL, for every single row,
-- so the AND can never evaluate to TRUE.

Every row has to be compared against that NULL, and that comparison is NULL rather than TRUE. TRUE AND NULL is NULL, so the full condition is never TRUE for anybody, so WHERE keeps nobody. The operator is working exactly as specified.

The single most useful thing in this episode

If you write NOT IN against a subquery whose column allows NULLs, you have written a query that can silently return nothing. Use NOT EXISTS, which is unaffected, or add WHERE <column> IS NOT NULL to the subquery. If you take one thing from this page, take this one.

Now let's try the same shape with no NULL in the list

The operator itself is fine. Let's prove it by running the same query against a list drawn from DEPARTMENTS, where the column holds no NULL.

Query

SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID NOT IN (SELECT DEPT_ID FROM DEPARTMENTS WHERE DEPT_ID = 40);

Result7 rows · 2 columns

FIRST_NAME	DEPT_ID
Aisha	10
Marcus	10
Lena	20
Tomas	20
Priya	30
Daniel	10
Sofia	30

Seven rows, and the query behaves. But count them against the eight in the table and notice who is still absent. Grace.

The NULL is out of the list now, but it is still in her row. NULL NOT IN (40) is NULL for the same reason as before.

Two NULLs, two symptoms

Two different NULL problems live in the same operator, and fixing one does not fix the other. A NULL in the list destroys the whole result. A NULL in the column you are testing quietly drops that one row. The second is the bug that has been eating Grace since Episode 3.

One last thing: let's see where the NULLs sort

There is one more place NULL shows up, and that is ordering. Let's run a plain ORDER BY on a column that contains one.

Query

SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES ORDER BY DEPT_ID;

Result8 rows · 2 columns

FIRST_NAME	DEPT_ID
Aisha	10
Marcus	10
Daniel	10
Lena	20
Tomas	20
Priya	30
Sofia	30
Grace	NULL

In this account, on this run, the NULL came last. That is what happened, and it is not a rule you may rely on.

The documentation is explicit about what it actually depends on: “When NULLS FIRST or NULLS LAST isn’t specified, the ordering of NULL values depends on the setting of the DEFAULT_NULL_ORDERING parameter and the sort order.”

That is a session parameter. Somebody can change it, and our report quietly reorders itself.

So if the position of the NULLs matters to us, we have to say which one we want. NULLS FIRST or NULLS LAST, written into the query. That is exactly where the next post starts.

Key points

• NULL is not a value. It means there is no value here. Not zero, and not an empty string.
• NULL = NULL returns NULL, and NULL != NULL returns NULL too. Only IS NULL returns TRUE.
• WHERE keeps a row only when the condition is TRUE, so WHERE DEPT_ID = NULL returns zero rows even when a NULL DEPT_ID exists.
• IS NULL and IS NOT NULL are the only reliable test for absence, and they are what the documentation tells you to use.
• NULL spreads through arithmetic, because anything times NULL is NULL. It spreads through concatenation too, where one NULL piece makes the whole string NULL.
• COALESCE returns the first argument that is not NULL, which stops the spread. Decide first whether substituting a default changes the question you are answering.
• COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. The gap between them is how an average goes quietly wrong.
• NOT IN against a subquery whose column allows NULLs can return zero rows with no error. Use NOT EXISTS, or filter the NULLs out of the subquery.
• A NULL in the column you are testing also drops that one row from NOT IN. Removing the NULL from the list does not fix it.
• Where NULLs sort without NULLS FIRST or NULLS LAST depends on the DEFAULT_NULL_ORDERING session parameter. Say which you want.

Next in this series

In the next post we will cover ORDER BY: ASC and DESC, sorting on several keys, ordinals and aliases, and NULLS FIRST and NULLS LAST. That is the explicit answer to the question this page refused to turn into a rule.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the script above. The EMPLOYEES, DEPARTMENTS and ORDERS data is invented demo data created by the course setup script. No performance or benchmark claims are made anywhere in this series. Behaviour of NULL in a predicate is quoted from the Snowflake WHERE clause documentation; the NULL ordering caveat is quoted from the ORDER BY documentation.