CODEWITHSUNDEEP
sqlnullsnowflake-cloud-data-platformsqldatatypes
x89
x89

Reputation: 3460

NULL values not filtered out with WHERE statement

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.

Upvotes: 2

Views: 2374

Answers (3)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25928

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)

Upvotes: 1

Elliot
Elliot

Reputation: 11

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

Upvotes: 0

Piyush Kachhadiya
Piyush Kachhadiya

Reputation: 622

use this for only want null recode

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NULL
or
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') = ''

if you get not null value then use

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') <> ''
or 
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NOT NULL

Upvotes: 3

Related Questions