SQL NULL Behavior: Key Rules Every Data Engineer Must Know

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

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

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

A couple of fun things about NULL in SQL

Comments

Practical SQL

FLOAT vs NUMERIC in BigQuery

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

FLOAT vs NUMERIC in BigQuery

More from this blog