NULL values not filtered out with WHERE statement

Question

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE 

When I select these columns, I see that the results have some NULL values. They don't seem to be strings. However, when I try to filter the NULL values out with a where statement:

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME = NULL

I don't see any results. What might be the possible causes? I also tried filtering with 'NULL' but that would throw an error since the column type is double.

Answer 1

You can use the where is function or is not function to filter all the null values.

Answer 2

a more full answer is NULL is not directly comparable, much like NaN is not comparable in floating point numbers. Both represent the "lack of a value" if you "have not value here" how can you compare it to something.

"There is nobody next to you, what is their name?" it just doesn't make sense.

So to test you ask column IS NULL or column IS NOT NULL or you can use a compact logic expression see Conditional Expressions but some common ones in Snowflake are:

short form	ANSI long	snowflake long
NVL(column,'')	CASE WHEN column IS NOT NULL THEN column ELSE '' END	IFF(column IS NOT NULL, column, '')
NVL2(column,'a','b')	CASE WHEN column IS NOT NULL THEN 'a' ELSE 'b' END	IFF(column IS NOT NULL, 'a', 'b')
ZEROIFNULL(column)	CASE WHEN column IS NOT NULL THEN column ELSE 0 END	IFF(column IS NOT NULL, column, 0)

COALESCE/NVL/IFNULL are all interchangable so I will only show one (expect COALESCE can handle N items which are checked in order)