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

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'

CodePudding user response：

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

CodePudding user response：

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'