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.

Found it useful? Subscribe to my Analytics newsletter at https://www.notjustsql.com.

---

Enjoyed this? Here are some related articles you might find useful:

• A couple of fun things about NULL in SQL
• COALESCE vs IFNULL vs NULLIF in BigQuery
• Null-safe comparison: IS DISTINCT/NOT DISTINCT FROM
• Controlling ordering of NULL values in the ORDER BY clause