Why does Snowflake exclude matching NULL values in WHERE clause?

Question

Suppose I have a table called people where one of the values in the Name column is null:

ID	Name
1	John
2	Mary
3	null

I want to select all rows where Name is not equal to "John", so I do:

SELECT * FROM people WHERE Name <> 'John'

In Snowflake, this returns only "Mary" and excludes the last row with a null value for Name. If I want to include null values (expected behavior), I have to do:

SELECT * FROM people WHERE (Name <> 'John' OR Name is NULL)

Is this expected behavior? And does anyone know how to configure Snowflake to return NULL rows as well?

Since by definition, the absence of a value (i.e. NULL) should match the condition Name <> 'John'

Answer 1

All databases handle NULLs the same way (well, pretty much so). Almost any comparison involving NULL -- with the notable exceptions of IS NULL and IS NOT NULL and a few others -- returns NULL. WHERE clauses and CASE expressions treat NULL values the same as "false".

Fortunately, Snowflake also implements the standard SQL NULL-safe operator, IS DISTINCT FROM. So you can write the logic as:

SELECT p.*
FROM people p
WHERE Name IS DISTINCT FROM 'John'

Answer 2

It is common database expected behaviour when every comparison with null returns false.

In other words:

if some_variable = null -- always false even if some_variable was assigned null
if some_variable <> null -- always false

It is because "null" means "nothing" and it is hard to compare nothing to nothing.

As I can see from the docs you can avoid writing "or name is null" by using NVL function which takes 2 parameters and returns second parameter if first one is null.

For example:

nvl(1, 3) = 1
nvl(null, 3) = 3

So your query might be looking like this:

SELECT * FROM people WHERE nvl(Name,'John') <> 'John';