Understanding NULL in BigQuery SQL

NULL in SQL represents the absence of a value and is distinct from an empty string or zero. Here are some key points about NULL:

Characteristics of NULL

• Testing NULL in Lists: NULL IN (NULL) returns NULL.
• Equality Checks: Both (NULL = NULL) and (NULL <> NULL) are not allowed.
• Counting Rows: COUNT(column) counts non-null occurrences, while COUNT(*) or COUNT(1) counts all rows, including those with NULLs.
• Aggregate Functions: Functions like SUM, COUNT, MIN, MAX, and AVG ignore rows with NULLs.
• Sorting: By default, ORDER BY shows NULLs first when sorting in ascending order.
• Joins: NULL values are not matched in joins since NULL is not comparable to another NULL.

Handling NULLs

• IS NULL / IS NOT NULL: Checks if a value is or is not NULL.
• COALESCE: Returns the first non-null value in a list.
• IFNULL / ISNULL: Uses a backup value if the original is NULL.
• NULLIF: Replaces a specified value with NULL.