NULL Handling
Work with missing or unknown values.
Questions this answers
- →How do I find rows where a value is missing?
- →Why does WHERE col = NULL not work?
- →How do I replace NULLs with a default value?
- →How do NULLs affect aggregates?
How it works
NULL means "no value" — not zero, not an empty string, not false. NULL is unknown. Because of this, comparing NULL to anything (including another NULL) returns NULL, not TRUE or FALSE. This trips up almost everyone the first time.
Find rows with missing values
SELECT name FROM artists WHERE label IS NULL;
💡 Never use = NULL. Use IS NULL or IS NOT NULL.
Replace NULLs with COALESCE
SELECT name, COALESCE(label, 'Independent') AS label FROM artists;
💡 COALESCE returns the first non-NULL argument. Use it to substitute defaults.
NULLs in aggregates
-- COUNT(*) counts all rows including NULLs -- COUNT(col) skips NULLs SELECT COUNT(*), COUNT(label) FROM artists;
💡 AVG, SUM, MIN, MAX all skip NULLs. COUNT(*) does not.