A couple of fun things about NULL in SQL

If you ever worked with SQL, even a tiny bit, you know NULL is very special value. Like no other. It's very different than an empty string '' or 0, rather representing the absence of a value.

A couple fun things about it:
🔹 you cannot test if NULL is in a list of values: NULL IN (NULL) returns a NULL
🔹 both (NULL = NULL) and (NULL <> / != NULL) are not allowed
🔹 COUNT(column) counts non-null occurrences in a column, whereas
COUNT(*) or COUNT(1) counts all rows, including those with NULLS
🔹 main aggregate functions ignore such SUM, COUNT, MIN, MAX, AVG ignore rows with NULLS; NULL is not the smallest value, it's just NULL, but
🔹 ORDER by shows NULLS first by default when sorting ascending
🔹 since a NULL not equal (not even comparable) to another NULL, upon joining, NULL values are not going to be matched

You can handle NULLS with:
🔹 x IS NULL/ IS NOT NULL : checks if something is or is not a NULL
🔹 COALESCE: take first non-null value in a list of values
🔹 IFNULL/ISNULL: if null, use a backup value
🔹 NULLIF: replace this value with a NULL

WITH source_a AS (

  SELECT 1 AS order_id, 'UK' AS country
  UNION ALL
  SELECT 2 AS order_id, 'US' AS country
  UNION ALL
  SELECT NULL AS order_id, 'FR' AS country
),
source_b AS (

  SELECT 1 AS order_id, 'apples' AS product
  UNION ALL
  SELECT 2 AS order_id, 'peaches' AS product
  UNION ALL
  SELECT NULL AS order_id, 'grapes' AS products

)
SELECT
order_id, country, product

FROM source_a
FULL OUTER JOIN source_b USING(order_id)

---

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

• Not all NULLS are the same
• 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