SELECT empty values in snowflake select and count statement

MTALY 0
Viewed 36

This is my first time handling a snowflake table, but I have a difficulty selecting empty values in my SQL query.

This is an example table:

enter image description here

I am selecting count of customers whom registrations is NOT TRUE. I used this query:

SELECT count(*) FROM CUSTOMERS WHERE Registered != 'TRUE'

I got only two customers because of empty (NULL) values, then I tried another query with NULLIF to include nulls:

SELECT count(NULLIF(Registered,'')) AS TEST FROM CUSTOMERS WHERE Registered != 'TRUE'

This also did not include the empty values in the total count!

snowflake-cloud-data-platform database sql
1 Answers

Using IS DISTINCT FROM null-safe comparison:

SELECT COUNT(*) 
FROM CUSTOMERS 
WHERE Registered IS DISTINCT FROM 'TRUE';

In this scenario the condition could be part of conditional aggregation:

SELECT 
   COUNT_IF(Registered IS DISTINCT FROM 'TRUE') AS count_not_true_and_nulls,
   COUNT_IF(Registered != 'TRUE') AS count_not_true_without_nulls
FROM CUSTOMERS;

Output:

enter image description here

Lukasz Szozda0
Related