BigQuery/SQL: If value is NULL, then (value != 'some string') returns false

Question

This query returns only the first line. But NULL is not equal to "anything", why on earth would it return FALSE? Is this some kind of bug? This logic seems counterintuitive.

WITH sample AS (
  SELECT 'something' AS key
  UNION ALL
  SELECT NULL AS key
)

SELECT * FROM sample WHERE key != 'anything'

where clause: Only rows whose bool_expression evaluates to TRUE are included. Rows whose bool_expression evaluates to NULL or FALSE are discarded. Operators: Unless otherwise specified, all operators return NULL when one of the operands is NULL. — astentx
– astentx, Commented Jan 31, 2022 at 5:16
yes, I figured this. My question is why NULL != 'string' is not TRUE? — stkvtflw
– stkvtflw, Commented Jan 31, 2022 at 5:17

Mikhail Berlyant · Accepted Answer · 2022-01-31 05:45:20Z

5

Unless otherwise specified, all operators return NULL when one of the operands is NULL

So, NULL != 'string' returns NULL, which is obviously not a TRUE (nor FALSE) and thus being excluded from output

You can see it by yourself, by running

SELECT *, key != 'anything' 
FROM sample

That is why you should use IFNULL(key, '') != 'anything'

You can see difference by running

SELECT *, key != 'anything', ifnull(key, '') != 'anything' 
FROM sample

P.S. You can see more about BigQuery Operators

What's the benefit of having this kind of logic? Why NULL != 'anything' is not TRUE?

The SQL null value basically means “could be anything”. It is therefore impossible to tell whether a comparison to null is true or false. This logic is an integral part of Core SQL and it is followed by pretty much every SQL database

Think of null as a missed/absent data the value of which can be anything, thus result of comparison (or other operations) is unknown, which is what null is

edited Jan 31, 2022 at 5:45

answered Jan 31, 2022 at 5:31

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

stkvtflw Over a year ago

Thank you. Do you know why is it designed this way? What's the benefit of having this kind of logic? Why NULL != 'anything' is not TRUE?

Mikhail Berlyant Over a year ago

The SQL null value basically means “could be anything”. It is therefore impossible to tell whether a comparison to null is true or false. This logic is an integral part of Core SQL and it is followed by pretty much every SQL database

Mikhail Berlyant Over a year ago

Think of null as a missed/absent data the value of which can be anything, thus result of comparison (or other operations) is unknown, which is what null is

astentx Over a year ago

@stkvtflw It is a standard convention in SQL: null is neither equal nor not equal to anything (including null)

Mikhail Berlyant Over a year ago

@stkvtflw - if answer helped please accept it and vote it up if not yet :o)

Piyush Kachhadiya · Accepted Answer · 2022-01-31 05:14:28Z

1

When you work with Null Value Then use ISNULL to setup you want to do. it's not bug.

WITH sample AS (
  SELECT 'something' AS [key]
  UNION ALL
  SELECT NULL AS [key]
)

SELECT * FROM sample WHERE ISNULL([key],'') != 'anything'

answered Jan 31, 2022 at 5:14

Piyush Kachhadiya

6321 gold badge8 silver badges19 bronze badges

2 Comments

stkvtflw Over a year ago

thank you. But why is this a thing? NULL is not equal to any NOT NULL value.

Piyush Kachhadiya Over a year ago

A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

Collectives™ on Stack Overflow

BigQuery/SQL: If value is NULL, then (value != 'some string') returns false

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related