Difference between x = null vs. x IS NULL

Question

In Snowflake, what is the difference between x = NULL and x IS NULL in a condition expression? It seems empirically that x IS NULL is what I want when I want to find rows where some column is blank. I ask because x = NULL is treated as valid syntax and I am curious whether there's a different application for this expression.

Answer 1

what is the difference between x = NULL and x IS NULL

In Snowflake just like in other RDBMS, Nothing is equal to NULL (even NULL itself), so a condition x = NULL (which is valid SQL syntax) will always evaluate as false (well, actually, it evaluates to NULL in most RDBMS, which is not true). Note that this is also true for non-equality comparisons: that is NULL <> NULL is false too.

The typical way to check if a variable is NULL is to use the x IS NULL construct, which evaluate as true if x is NULL. You can use x IS NOT NULL too. This syntax is reserved for NULL, so something like x IS y is a syntax error.

Here is a small demo:

select 
    case when 1 = null then 1 else 0 end 1_equal_null,
    case when 1 <> null then 1 else 0 end 1_not_equal_null,
    case when null is null then 1 else 0 end null_is_null,
    case when 1 is not null then 1 else 0 end 1_is_not_null

1_equal_null | 1_not_equal_null | null_is_null | 1_is_not_null
-----------: | ---------------: | -----------: | ------------:
           0 |                0 |            1 |             1