A couple of fun things about NULL in SQL


1 min read

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

Image preview

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