# 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

![](https://miro.medium.com/v2/resize:fit:1296/0*2OJE2DjSL3XkI7KH align="left")

*Found it useful? Subscribe to my Analytics newsletter at* [*notjustsql.com*](https://notjustsql.com)*.*

---

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

- [Not all NULLS are the same](https://datawise.dev/not-all-nulls-are-the-same)
- [COALESCE vs IFNULL vs NULLIF in BigQuery](https://datawise.dev/coalesce-vs-ifnull-vs-nullif-in-bigquery)
- [Null-safe comparison: IS DISTINCT/NOT DISTINCT FROM](https://datawise.dev/null-safe-comparison-is-distinctnot-distinct-from)
- [Controlling ordering of NULL values in the ORDER BY clause](https://datawise.dev/controlling-ordering-of-null-values-in-the-order-by-clause)