Not all NULLS are the same
So NULLs are definitely beasts of their own and as Data Engineers we come to learn to take them into account.
That is because not knowing their quirks can lead to unexpected results or errors. Let's look at how not all NULLS are the same in BigQuery SQL.
First, sure, the NULL means the absence of a value, but it is bound to a particular data type (so it's like "i'm a missing an INT64 here"). It can be found in columns with the NULLABLE mode, therefore a DATETIME NULL and a NUMERIC NULL cannot be compared as they are different types.
I've also seen that if we specify just the NULL literal, it defaults to INTEGER.
--No matching signature for operator != for argument types: INT64, STRING. Supported signature: ANY != ANY at [4:8]
--SELECT CAST(NULL AS INT64) <> CAST(NULL AS STRING)
-- If not specified, the NULL is considered an INT
CREATE OR REPLACE TABLE `learning.my_table` AS
SELECT NULL AS my_column;
--null
SELECT my_column <> CAST(NULL AS INT64) FROM learning.my_table;
Enjoyed this? Here are some related articles you might find useful: