NULL Handling
Beginner⏱️ 10 mins read
What You'll Learn
NULL represents missing or unknown data — not zero, not empty string. NULL propagates: any arithmetic with NULL returns NULL. Comparisons with NULL always return UNKNOWN. Use COALESCE to replace NULL with a default. Use NULLIF to return NULL when two values are equal.
Syntax
WHERE col IS NULL
WHERE col IS NOT NULL
COALESCE(col, default_value)
NULLIF(col, value)Example
-- Check for NULL (NEVER use = NULL)
SELECT * FROM employees WHERE manager_id IS NULL;
-- Replace NULL with default
SELECT name, COALESCE(phone, 'No Phone') AS contact
FROM customers;
-- NULLIF: returns NULL if score = 0 (avoid division by zero)
SELECT name, total / NULLIF(attempts, 0) AS avg_score
FROM results;
-- COUNT(*) vs COUNT(col) — may differ!
SELECT COUNT(*), COUNT(email) FROM users;Common Mistakes
COUNT(*) counts ALL rows including NULLs. COUNT(email) ignores NULL emails — they can return different numbers. Also: NULL + 5 = NULL. NULL = NULL is UNKNOWN (not TRUE). This is why WHERE col = NULL never matches.
Interview Tips
This is a frequent gotcha question. Be ready to explain three-valued logic in SQL (TRUE, FALSE, UNKNOWN). Mention that NOT IN with a subquery containing NULLs always returns empty — a classic bug.
Practice
Find all employees without a manager. Show their salary, replacing NULL salary with 0. Also show the count of employees with vs without a phone number.