Working with NULL
Handle missing and unknown values
NULL represents missing, unknown, or inapplicable data. It's not the same as zero, an empty string, or false. Understanding NULL is crucial for writing correct SQL.
NULL is Special
- -NULL is not equal to anything, including itself
- -Any comparison with NULL returns NULL (not true or false)
- -Math with NULL returns NULL
Checking for NULL
-- WRONG: This doesn't work! SELECT * FROM users WHERE phone = NULL; -- Never returns rows SELECT * FROM users WHERE phone != NULL; -- Never returns rows -- CORRECT: Use IS NULL / IS NOT NULL SELECT * FROM users WHERE phone IS NULL; -- Users without phone SELECT * FROM users WHERE phone IS NOT NULL; -- Users with phone
COALESCE - Default Values
Returns the first non-NULL value from a list:
-- Use default when NULL
SELECT
name,
COALESCE(phone, 'No phone') AS phone,
COALESCE(nickname, name) AS display_name
FROM users;
-- Try multiple fallbacks
SELECT COALESCE(mobile, home_phone, work_phone, 'N/A') AS contact
FROM users;NULLIF - Create NULL Conditionally
Returns NULL if two values are equal:
-- Convert empty string to NULL SELECT NULLIF(phone, '') AS phone FROM users; -- Prevent division by zero SELECT total / NULLIF(quantity, 0) AS unit_price FROM orders;
NULL in Aggregations
-- COUNT(*) counts all rows
-- COUNT(column) ignores NULL values
SELECT
COUNT(*) AS total_users,
COUNT(phone) AS users_with_phone,
COUNT(*) - COUNT(phone) AS users_without_phone
FROM users;
-- AVG, SUM, etc. ignore NULL values
SELECT AVG(rating) FROM reviews; -- NULLs not included in averageCommon Mistake: Using = NULL instead of IS NULL. This is the #1 SQL bug for beginners!