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.
+------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+ | 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 | +------+------+--------------------------------+------------------------------------------+----------------------------+--------------------------------------+