SQL Bigquery returning null results even though I am using IS NOT NULL statement

Question

In Bigquery, I am receiving rows where the value is Null even though I have placed an IS NOT NULL statement in my query.

SELECT 
country,
region,
region_name
FROM `table`
WHERE region_name IS NOT NULL
AND country LIKE 'DE'
GROUP BY 2, 3, 1

When I pull this however, I still receive a row where region and region name are NULL but country is not. Is there something obvious I am missing here?

score 4 · Accepted Answer · answered May 22 '17 at 16:19

4

Maybe you are observing empty strings that are not NULL. One way to confirm this is by adding in your WHERE clause:

WHERE
region_name IS NOT NULL and region_name != ''
AND country LIKE 'DE'

answered May 22 '17 at 16:19

Willian Fuks

11,259
10
50
74

Wow that is amazing. worked for me! Why does bigquery not treat an empty string as a NULL? whats the difference? – Alex May 22 '17 at 16:52
glad it worked :). Check this out for a better understanding of their difference: https://stackoverflow.com/questions/40651685/empty-string-vs-null – Willian Fuks May 22 '17 at 16:56
If the value is the string, `null`, it will render as _null_ in the BQ UI, misleading the user into believing that the value is `NULL` – Brian Bien Mar 25 '22 at 12:59

SQL Bigquery returning null results even though I am using IS NOT NULL statement

1 Answers1