CODEWITHSUNDEEP
sqlgoogle-bigquerywhere-clausesql-null
sunilSAP
sunilSAP

Reputation: 61

Problem with Big Query filters when your data has a null in a column

I am facing a strange issue starting today in Google Big Query. The below query is returning no records. I did not see this issue yesterday

WITH Q1 AS (SELECT 'XC1' AS XC , NULL AS NU 
UNION ALL
SELECT 'XC2' AS XC , 'TAB' AS NU )

SELECT * FROM Q1
WHERE NU != 'TAB'

It should ideally show the record with XC = XC1 but it does not return any records.

Are you all facing the same issue ? Please let us know soon as we are facing a bigger issue in other areas.

Can you suggest what is going on here ?

Upvotes: 0

Views: 1246

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Yet another option to address this case for BigQuery Standard SQL is 

WHERE IFNULL(NU != 'TAB', TRUE)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

BigQuery doesn't have a NULL-safe operator. It does have a similar method, though:

where not ((nu = 'TAB') is false)

I will admit that the or solution is probably clearer.

Upvotes: 0

GMB
GMB

Reputation: 222482

You are looking for a null-safe comparison. In standard SQL, you would typically use operator IS [NOT] DISTINCT FROM:

SELECT * FROM Q1 WHERE NU IS DISTINCT FROM 'TAB'

Unfortunately few database support this syntax, and BQ is not one of them. So we need to resort a condition expression:

SELECT * FROM Q1 WHERE NU IS NULL OR NU != 'TAB'

Upvotes: 0

Related Questions