Null-safe comparison: IS DISTINCT/NOT DISTINCT FROM

I've been working for surprisingly long with SQL to have found this only a few days ago. Not long enough I guess 🤓.

I'm talking about the NULL-safe operators IS DISTINCT FROM and IS NOT DISTINCT FROM. I found about their existence from a Linkedin post.

Works on BigQuery too, so I guess less need of adding IFNULLs / COALESCE for safety.

SELECT
  'a' <> CAST(NULL AS STRING) AS a_different_null,
  'a' = CAST(NULL AS STRING) AS a_equals_null,
  'a' IS DISTINCT FROM CAST(NULL AS STRING) AS a_distinct_null,
  'a' IS NOT DISTINCT FROM CAST(NULL AS STRING) AS a_not_distinct_null

PS This choice of keyword "FROM", together with the one in EXTRACT(HOUR FROM DATETIME '2021-01-01'), feels pretty weird.

---

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

• A couple of fun things about NULL in SQL
• Not all NULLS are the same
• COALESCE vs IFNULL vs NULLIF in BigQuery
• Controlling ordering of NULL values in the ORDER BY clause