NULL doesn't behave like a normal value, and it quietly breaks queries that "look" correct. This trips up even experienced engineers.
customers
+----+---------+---------+
| id | name | phone |
+----+---------+---------+
| 1 | Alice | 555-1234|
| 2 | Bob | NULL |
| 3 | Charlie | NULL |
⚠️ Trap #1:
WHERE phone = NULL returns ZERO rows - always. NULL means "unknown," and "is unknown equal to unknown?" is itself unknown, not true. You must use IS NULL:sql
SELECT * FROM customers WHERE phone IS NULL; -- ✅ correct
⚠️ Trap #2:
COUNT(phone) vs COUNT(*) give different results. COUNT(*) counts all rows; COUNT(column) only counts non-NULL values in that column.sql
SELECT COUNT(*) FROM customers; -- 3
SELECT COUNT(phone) FROM customers; -- 1
⚠️ Trap #3: NULL values are often silently EXCLUDED from aggregate calculations in ways people don't expect:
sql
SELECT AVG(phone_call_count) FROM customers;
-- NULLs are ignored entirely, NOT treated as 0.
-- If you wanted them treated as 0, use:
SELECT AVG(COALESCE(phone_call_count, 0)) FROM customers;
COALESCE(value, default) returns the first non-NULL argument - extremely useful for handling missing data gracefully instead of letting it silently skew your results.Has a NULL-related bug ever quietly thrown off a real report at your job? 👇