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.
Advertisement
Answer
This particular case is well-described in Snowflake’s documentation:
Compares whether two expressions are equal. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality. Note that this is different from the EQUAL comparison operator (=), which treats NULLs as unknown values.
x
+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+| X1_I | X2_I | X1.I IS NOT DISTINCT FROM X2.I | SELECT IF X1.I IS NOT DISTINCT FROM X2.I | X1.I IS DISTINCT FROM X2.I | SELECT IF X1.I IS DISTINCT FROM X2.I ||------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------|| 1 | 1 | True | Selected | False | Not || 1 | 2 | False | Not | True | Selected || 1 | NULL | False | Not | True | Selected || 2 | 1 | False | Not | True | Selected || 2 | 2 | True | Selected | False | Not || 2 | NULL | False | Not | True | Selected || NULL | 1 | False | Not | True | Selected || NULL | 2 | False | Not | True | Selected || NULL | NULL | True | Selected | False | Not |+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+