Snowflake SQL Tutorial 3 - WHERE Clause Explained - Comparison Operators + The NULL Trap

Snowflake SQL · Episode 3

In this post we are going to cover the WHERE clause in Snowflake.

WHERE is how we pick the rows we want. We give it a condition, Snowflake tests that condition against every row in the table, and we get back the rows it was true for.

We will start with one simple condition, then go through all the comparison operators one at a time. At the end we will split the table in two with a condition and its opposite, count the rows, and find out why they do not add up.

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

Episode 3 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 one simple filter
3. Now let's filter on text
4. Now let's go through the comparison operators
5. Let's write not equal to, both ways
6. Now let's compare dates
7. Can the condition be a calculation?
8. Now let's split the table in two and count the rows
9. Let's get the missing row back with IS NULL

Let's create some sample data

Everything on this page runs against one small table called EMPLOYEES. The script below creates it and fills it, so you can paste it into a Snowflake worksheet and follow along. No sample database is needed.

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');

It is the same table the whole series uses, so you only ever set it up once.

Let's look at four of its columns with no filter on them at all. This is the baseline for every query below.

Query — no WHERE clause

SELECT FIRST_NAME, LAST_NAME, DEPT_ID, SALARY
FROM EMPLOYEES;

Result8 rows · 4 columns

FIRST_NAME	LAST_NAME	DEPT_ID	SALARY
Aisha	Khan	10	118000.00
Marcus	Reid	10	96500.00
Lena	Fischer	20	104000.00
Tomas	Novak	20	87250.00
Priya	Raman	30	96500.00
Daniel	O'Connor	10	131000.00
Sofia	Marino	30	71400.00
Grace	Lin	NULL	92000.00

That is everything the table holds. Have a look at the DEPT_ID on the last row. Grace has no department, so the column says NULL.

That is not decoration. It is the whole point of the last two sections of this post.

Let's start with one simple filter

We add the word WHERE after the table name, then a condition. Let's ask for the employees in department 10.

Query

SELECT FIRST_NAME, LAST_NAME, DEPT_ID, SALARY
FROM EMPLOYEES
WHERE DEPT_ID = 10;

Result3 rows · 4 columns

FIRST_NAME	LAST_NAME	DEPT_ID	SALARY
Aisha	Khan	10	118000.00
Marcus	Reid	10	96500.00
Daniel	O'Connor	10	131000.00

Eight rows became three: Aisha, Marcus and Daniel. Everybody whose DEPT_ID is not 10 has gone.

The documentation describes the clause in a single line. It “specifies a condition that acts as a filter”.

Here is the way to picture it, and the rest of this post is a consequence of it. Snowflake takes the condition and evaluates it against every row, one row at a time. If the answer is true, the row is in our result. If it is anything other than true, the row is not.

Hold on to those last four words. “Anything other than true” is doing more work in that sentence than it looks like it is.

Now let's filter on text

= works on text exactly as it does on numbers. A string goes in single quotes. Let's look for Khan.

Query

SELECT FIRST_NAME, LAST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'Khan';

Result1 row · 3 columns

FIRST_NAME	LAST_NAME	DEPT_ID
Aisha	Khan	10

One row, which is what we expected.

Now let's type the same name in capitals

We will change nothing except the case of the value.

Query

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME = 'KHAN';

Result0 rows · 2 columns

FIRST_NAME	LAST_NAME

Zero rows. String comparison in Snowflake is case sensitive, so 'KHAN' is simply not the same string as 'Khan'.

An empty result is not the same as no data

This is a trap precisely because nothing goes wrong. There is no error, no warning and no hint. We get an empty result, which looks exactly like no matching data, and then we go looking for the problem in the pipeline, the load, or the source system. It was the capital letters all along.

Quotes are not interchangeable

Single quotes make a string literal. Double quotes mean something else entirely in Snowflake. They make a quoted identifier, so "Khan" asks for a column named Khan and not a value. That is Episode 12.

Now let's go through the comparison operators

These are the operators WHERE gives us. The list itself holds no surprises.

Operator	Means	Note
=	Equal to	Case sensitive on strings
!=	Not equal to	Identical to <>
<>	Not equal to	Identical to !=
>	Greater than	Excludes the boundary value
<	Less than	Excludes the boundary value
>=	Greater than or equal to	Includes the boundary value
<=	Less than or equal to	Includes the boundary value

Two of them are worth running rather than reading, because they look almost the same as the operator next to them. Let's take them in order.

Greater than

Query

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 100000;

Result3 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Lena	104000.00
Daniel	131000.00

Three people earn more than 100,000.

Now let's use greater than or equal to

In a reference manual this reads like a technicality. On real data it is not. Let's filter at a salary two of our employees earn exactly.

Query

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY >= 96500;

Result5 rows · 2 columns

FIRST_NAME	SALARY
Aisha	118000.00
Marcus	96500.00
Lena	104000.00
Priya	96500.00
Daniel	131000.00

Five rows, not three. Two employees earn exactly 96,500, and >= includes them while > would have thrown both away. If you mean at least, do not type >.

And less than

Query

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY < 90000;

Result2 rows · 2 columns

FIRST_NAME	SALARY
Tomas	87250.00
Sofia	71400.00

Two rows. <= exists as well, and it relates to < exactly as >= relates to >.

Let's write not equal to, both ways

Snowflake accepts two spellings of not equal to. Let's start with !=.

Query

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID != 10;

Result4 rows · 2 columns

FIRST_NAME	DEPT_ID
Lena	20
Tomas	20
Priya	30
Sofia	30

Now let's write the same filter with the angle brackets.

Query

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID <> 10;

Result4 rows · 2 columns

FIRST_NAME	DEPT_ID
Lena	20
Tomas	20
Priya	30
Sofia	30

The same rows. != and <> are the same operator, not two operators with similar behaviour, so there is nothing to choose between them on correctness.

Use whichever spelling your team already uses, and use it consistently. Mixing both in one codebase makes a search for every inequality in your SQL harder than it needs to be.

Remember this one

Hold on to the row count from these two queries. It is the first half of the arithmetic that does not work, further down this page.

Now let's compare dates

These are not numeric operators. They work on dates as readily as on numbers and text. A date literal is a string in single quotes, in YYYY-MM-DD form.

Query

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE > '2021-01-01';

Result4 rows · 2 columns

FIRST_NAME	HIRE_DATE
Marcus	2021-07-01
Tomas	2022-09-05
Sofia	2023-02-13
Grace	2021-04-19

The four employees hired after 2021-01-01. On a date, > means later than and < means earlier than. The operator did not change, only the type it is comparing.

Can the condition be a calculation?

Every query so far has compared a plain column against a value, which is what most WHERE clauses look like. It is not what the clause requires.

The documentation calls the condition “A Boolean expression. The expression can include logical operators, such as AND, OR, and NOT.” An expression, so the left-hand side can be worked out for us.

Let's take ten percent of the salary and test that against 10,000.

Query

SELECT FIRST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE SALARY * 0.10 > 10000;

Result3 rows · 3 columns

FIRST_NAME	SALARY	COMMISSION_PCT
Aisha	118000.00	NULL
Lena	104000.00	0.05
Daniel	131000.00	NULL

Three rows. COMMISSION_PCT is in the output because we selected it, and it played no part in the filter at all. Two of these three employees have no commission recorded and the third has 0.05, and none of that was consulted. The expression was built from SALARY.

Filtering and selecting are separate jobs

SELECT and WHERE are independent. A column can be filtered on without being selected, and selected without being filtered on. The expression in a WHERE clause does not have to appear in the result at all.

Now let's split the table in two and count the rows

This is the part worth slowing down for. Let's take the filter from the beginning of the post, this time on two columns.

Query — part 1

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID = 10;

Result3 rows · 2 columns

FIRST_NAME	DEPT_ID
Aisha	10
Marcus	10
Daniel	10

Three rows match DEPT_ID = 10. The table has eight rows, so DEPT_ID <> 10 should return the other five. Let's run it.

Query — part 2

SELECT FIRST_NAME, DEPT_ID
FROM EMPLOYEES
WHERE DEPT_ID <> 10;

Result4 rows · 2 columns

FIRST_NAME	DEPT_ID
Lena	20
Tomas	20
Priya	30
Sofia	30

Four. Not five. Three plus four is seven, and the table has eight rows. So one employee is in neither result, and rereading the two conditions does not explain it, because there is nothing wrong with either of them.

It is Grace. Her DEPT_ID is NULL, and NULL is not a value. It is the absence of one.

So the question is Grace's DEPT_ID equal to 10? is neither true nor false. It is unknown. And the question is it not equal to 10? is unknown as well. Unknown is not true, and WHERE only keeps the rows its condition is true for.

Why the arithmetic fails

The documentation states the consequence directly: “In a WHERE clause, if an expression evaluates to NULL, the row for that expression is removed from the result set.”
Removed. Not kept, not treated as false, not reported. Removed. So a condition and its own negation can both exclude the same row, and the two row counts will not add up to the table.

This matters well beyond a counting puzzle on eight rows. Any time we split a table into a group and “everybody else” with a condition and its negation, every row whose filtered column is NULL falls silently out of both halves. That is reconciling two reports, bucketing customers, validating a migration.

Let's get the missing row back with IS NULL

To get Grace back we need an operator that asks a question NULL can answer.

We cannot write DEPT_ID = NULL. From the documentation, “In most contexts, the Boolean expression NULL = NULL returns NULL, not TRUE”, and it recommends IS [NOT] NULL for comparing NULL values instead. Let's use that.

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. IS NULL does not compare the column to a value. It asks whether the column has a value at all, which is a question with a true-or-false answer.

NULL gets a whole post of its own, Episode 6, because the same three-valued logic turns up again in NOT IN, in joins and in aggregates. It is worth understanding once, properly.

Key points

• WHERE “specifies a condition that acts as a filter”: the condition is evaluated against every row, and the row is returned only when the answer is true.
• String comparison is case sensitive. 'KHAN' and 'Khan' are different strings, and the mismatch returns an empty result rather than an error.
• > excludes the boundary value and >= includes it. That is a real difference whenever somebody sits exactly on the number you filtered by.
• != and <> are the same operator, with nothing to choose between them but consistency.
• Comparison operators work on dates and text, not only numbers.
• The condition is “A Boolean expression”, so the left-hand side can be calculated. And a column can be filtered on without being selected.
• A condition and its negation do not have to cover the whole table. When an expression evaluates to NULL, “the row for that expression is removed from the result set” in both directions.
• Use IS NULL and IS NOT NULL to ask about missing values. = NULL returns NULL, not TRUE, so it never matches anything.

Next in this series

In the next post we will cover BETWEEN, IN, LIKE, ILIKE and RLIKE. These are the operators that make a WHERE clause much shorter to write: ranges, lists and pattern matching.

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 data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Clause descriptions are quoted from the Snowflake WHERE documentation.