Snowflake SQL Tutorial 5 - AND, OR, NOT Explained - The Operator Precedence Mistake Everybody Makes

Snowflake SQL · Episode 5

In this post we are going to cover AND, OR and NOT in Snowflake.

These are the three logical operators. We use them to join conditions together in a WHERE clause. AND wants both sides to be true. OR is happy with either side. NOT turns a condition over.

The three words are easy. The order Snowflake applies them in is not, and that is where this post is going. We will start with one operator at a time, and then we will write a query that runs perfectly and answers a different question from the one we asked.

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

Episode 5 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 AND
3. Now let's try OR
4. Now let's flip a condition with NOT
5. Now let's mix AND with OR in one query
6. Now let's see why Marcus came back
7. Let's make the brackets a habit
8. One last thing: let's wrap NOT around an AND

Let's create some sample data

Before we can test a condition we need a table to test it against. The script below creates a small EMPLOYEES table and puts eight rows into it.

Copy this into a Snowflake worksheet and run it once. Every post in this series uses the same table, so you only have to do this one time.

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

Two columns do all the work in this post. DEPT_ID and SALARY. Every condition below tests one or the other.

One row is worth remembering before we go any further. Marcus is in department 10 and earns 96500.00. He is the employee this whole post turns on. He shows up in a result he has no business being in, and the reason he is there is the lesson.

Keep an eye on

Grace has a NULL DEPT_ID. That matters at the very end of this page, and it is the whole subject of the next episode.

Let's start with AND

AND joins two conditions. It keeps a row only when both of them hold.

Let's ask for the people in department 10 who earn more than 100000.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY FROM EMPLOYEES WHERE DEPT_ID = 10 AND SALARY > 100000;

Result2 rows · 3 columns

FIRST_NAME	DEPT_ID	SALARY
Aisha	10	118000.00
Daniel	10	131000.00

Two rows came back, Aisha and Daniel.

Marcus is in department 10 as well, so he passed the first condition. But 96500.00 is not greater than 100000, so the second condition failed and his row was dropped. With AND, one failure is enough.

The documentation puts it in four words: “Matches both expressions (a and b).”

Now let's try OR

OR is the opposite bargain. A row survives if either condition holds.

Let's ask for everybody in department 20 or department 30.

Query

SELECT FIRST_NAME, DEPT_ID FROM EMPLOYEES WHERE DEPT_ID = 20 OR DEPT_ID = 30;

Result4 rows · 2 columns

FIRST_NAME	DEPT_ID
Lena	20
Tomas	20
Priya	30
Sofia	30

Four rows. Nobody in this table is in department 20 and department 30 at the same time. A row cannot be. So what we got back is the two departments added together.

The docs say it in three words: “Matches either expression.”

A shorter way to write this one

Testing one column against several values with a chain of ORs works, but it gets long fast. DEPT_ID IN (20, 30) says the same thing in fewer characters, and it appears again later on this page.

Now let's flip a condition with NOT

NOT takes a single condition and inverts it. The docs: “Doesn’t match the expression.”

Let's ask for everybody who is not in department 10.

Query

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

Result4 rows · 2 columns

FIRST_NAME	DEPT_ID
Lena	20
Tomas	20
Priya	30
Sofia	30

Four rows, and they are the same four people the OR query gave us. Everybody in department 10 has gone.

That is all three operators. Written out together they are as short as the documentation makes them look.

Operator	Keeps a row when	Snowflake documentation
AND	both conditions hold	“Matches both expressions (a and b).”
OR	at least one condition holds	“Matches either expression.”
NOT	the condition does not hold	“Doesn’t match the expression.”

Nothing in that table is surprising. Everything surprising in this post comes from what happens when we use two of them in the same WHERE clause.

Now let's mix AND with OR in one query

Here is a request in plain English. The people in departments 10 or 20 who earn more than 100000. Read it again, because the SQL we are about to write is not it.

Let's write it the way we just said it out loud. Department 10 or department 20, and then the salary test.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY FROM EMPLOYEES WHERE DEPT_ID = 10 OR DEPT_ID = 20 AND SALARY > 100000;

Result4 rows · 3 columns

FIRST_NAME	DEPT_ID	SALARY
Aisha	10	118000.00
Marcus	10	96500.00
Lena	20	104000.00
Daniel	10	131000.00

Four rows, and one of them should stop us. Marcus, on 96500.00.

That is not more than 100000. We asked for high earners and we got back somebody who is not one.

Watch out

This query did not fail, did not warn, and did not run slowly. It returned a clean, plausible-looking result to a question nobody asked. That is the dangerous shape of this bug — a syntax error you find in seconds; a wrong answer can sit in a report for a year.

Now let's see why Marcus came back

The query is not broken. It is answering exactly what it says. What it says is not what it looks like it says, because the operators are not applied left to right.

They have a documented precedence, highest first.

Precedence	Operator	Means
1 (highest)	NOT	applied before anything else
2	AND	binds its two sides together before OR sees them
3 (lowest)	OR	applied last, to whatever AND has already built

AND binds tighter than OR. So Snowflake reads our query with a pair of brackets in it that we never typed.

The invisible brackets

-- What you wrote:
WHERE DEPT_ID = 10 OR DEPT_ID = 20 AND SALARY > 100000

-- How Snowflake reads it, because AND binds tighter:
WHERE DEPT_ID = 10 OR (DEPT_ID = 20 AND SALARY > 100000)

Read the second version out loud and the result makes complete sense. Everybody in department 10, whatever they earn, plus the department 20 people who earn a lot.

Marcus is in department 10, so he came back on the left-hand side of the OR. The salary test never applied to him at all.

Now let's put the brackets where we meant them

The docs are explicit that this is ours to control: “You can use parentheses in the WHERE clause to change the precedence.”

So let's put them round the OR, which is the part the English sentence groups together.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY FROM EMPLOYEES WHERE (DEPT_ID = 10 OR DEPT_ID = 20) AND SALARY > 100000;

Result3 rows · 3 columns

FIRST_NAME	DEPT_ID	SALARY
Aisha	10	118000.00
Lena	20	104000.00
Daniel	10	131000.00

Three rows. Marcus has gone, and the salary test now applies to everybody instead of to the department 20 rows only.

Same operators, same values, one pair of brackets, a different answer.

Let's make the brackets a habit

The documentation says we can use parentheses to change the precedence. The advice on this page is a little stronger than that. When we mix AND with OR, let's write the brackets in even when they change nothing.

Both versions of that query are valid Snowflake. Only one of them tells the next person which one we meant. The next person is usually us, in nine months, looking at a number that is slightly off.

The other side of that is worth saying too. When every operator in the clause is an AND there is nothing to resolve, so brackets only add noise.

Let's try three conditions joined with AND.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY, HIRE_DATE FROM EMPLOYEES
WHERE DEPT_ID IN (10, 20) AND SALARY > 90000 AND HIRE_DATE < '2020-01-01';

Result3 rows · 4 columns

FIRST_NAME	DEPT_ID	SALARY	HIRE_DATE
Aisha	10	118000.00	2019-03-11
Lena	20	104000.00	2018-01-22
Daniel	10	131000.00	2016-05-30

Three rows. Department 10 or 20, earning over 90000, hired before 2020-01-01. The order those three conditions are evaluated in cannot change the answer, so the query reads cleanly without a bracket in sight.

Why IN sidesteps the problem

DEPT_ID IN (10, 20) is doing the work that DEPT_ID = 10 OR DEPT_ID = 20 did earlier. Because IN is a single condition rather than two joined by OR, there is no precedence question to get wrong — which makes it the quietest fix for the bug in this whole episode.

One last thing: let's wrap NOT around an AND

One last query. It is here to be looked at rather than fully explained, because the explanation needs the whole of the next post.

We are going to wrap NOT around a bracketed AND. That is the inverse of the very first query on this page.

Query

SELECT FIRST_NAME, DEPT_ID, SALARY FROM EMPLOYEES WHERE NOT (DEPT_ID = 10 AND SALARY > 100000);

Result6 rows · 3 columns

FIRST_NAME	DEPT_ID	SALARY
Marcus	10	96500.00
Lena	20	104000.00
Tomas	20	87250.00
Priya	30	96500.00
Sofia	30	71400.00
Grace	NULL	92000.00

Six rows. The first query gave us Aisha and Daniel. This one gives us everybody else, and that includes Grace, whose DEPT_ID is NULL.

That should bother us. In Episode 3 and Episode 4 a NULL DEPT_ID made Grace vanish from result after result. Here it does not.

The reason is genuinely subtle. Her salary test is 92000.00 against 100000, and that is FALSE. And FALSE AND anything is FALSE rather than unknown. So the bracket evaluates to FALSE, and NOT FALSE is TRUE.

The distinction that matters

NULL does not remove a row because a NULL is involved. It removes the row when the whole expression evaluates to NULL — and here it evaluated to FALSE. That distinction is the heart of three-valued logic and it gets the next episode to itself rather than a hand-wave here.

Key points

• AND keeps a row only when both conditions hold. OR keeps it when either does. NOT inverts a single condition.
• The documented precedence, highest to lowest, is NOT, then AND, then OR. It is not left to right.
• Because AND binds tighter, A OR B AND C is read as A OR (B AND C), so the C test never applies to the A rows.
• That mistake gives you a query that runs perfectly and answers a different question. No error, no warning, just a wrong number.
• Parentheses in the WHERE clause override the precedence, and they are the fix. Group the OR yourself.
• When you mix AND with OR, write the brackets even when they change nothing. Both forms are valid SQL. Only one records what you meant.
• A clause made entirely of ANDs needs no brackets. There is no ambiguity for them to resolve.
• IN (10, 20) is one condition rather than two joined by OR, so it cannot be mis-grouped in the first place.
• A NULL removes a row when the expression evaluates to NULL, not merely because a NULL took part. That is why the last query returns a row the earlier episodes lost.

Next in this series

In the next post we will cover NULL in Snowflake: three-valued logic, IS NULL, the NOT IN trap that can return zero rows, and the real reason that row kept going missing.

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. Operator descriptions and the precedence order are quoted from the Snowflake logical operators documentation.