How does not equal in snowflake exactly work?

Question

I have a snowflake query that has a field called status. The field either contains null or 'deleted'

when I do the following to get only deleted it works:

select * from tbl_1 where status = 'deleted'

when I try excluding all deleted it excludes everything, no records are returned. Here's what I've tried

select * from tbl_1 where status != 'deleted'

select * from tbl_1 where status <> 'deleted'

Neither one works. Can someone tell me why or what's the proper way of doing this in snowflake? Thanks in advance!

Answer 1

You can try the following which factors for the status being null and unsearchable for you.

select * from tbl_1 where IS_NULL_VALUE(status:no_value) != 'deleted'

https://docs.snowflake.com/en/sql-reference/functions/is_null_value.html

Answer 2

I think you should use EQUAL_NULL here. EQUAL_NULL 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.

https://docs.snowflake.com/en/sql-reference/functions/equal_null.html#equal-null

CREATE TABLE NULL_TEST(PRODUCT VARCHAR, STATUS VARCHAR);

INSERT INTO NULL_TEST VALUES('1111','DELETED');
INSERT INTO NULL_TEST VALUES('1112',NULL);


select * from NULL_TEST where NOT EQUAL_NULL(status,'DELETED');

Answer 3

Use NVL as it simply converts the NULL to another value that you can compare against

NVL doc here

I've tested using the below query to return values 2, 3 (that are not 'deleted')

Select * 
     from
    values (1,'deleted'),(2,NULL),(3,null),(4,'deleted')
      WHERE NVL($2,'N/A')!='deleted';

So for your case use SQL

select * from tbl_1 where NVL(status,'N/A') != 'deleted';