🌱BeginnerNULL & Conditional Handling
IS NULL and IS NOT NULL
Check for NULL values correctly using IS NULL and IS NOT NULL.
Overview
NULL represents the absence of a value it is not zero, not an empty string, and not FALSE. Any arithmetic or comparison involving NULL returns NULL. This is why col = NULL never matches any row: you must use col IS NULL.
NULL propagates through expressions: NULL + 5 = NULL, NULL AND TRUE = NULL, NULL OR FALSE = NULL (but NULL OR TRUE = TRUE). Understanding NULL behavior is essential because it affects COUNT (COUNT(*) counts all rows; COUNT(col) skips NULLs), aggregates, joins, and WHERE filters. Every experienced SQL developer has been bitten by unexpected NULLs at some point.
Key Points
- •IS NULL checks whether a value is NULL
- •IS NOT NULL checks whether a value is not NULL
- •col = NULL always returns NULL never use this for null checks
- •COUNT(*) counts all rows; COUNT(col) skips NULLs
- •Aggregates like SUM, AVG, MIN, MAX skip NULLs automatically
- •NULL in a WHERE condition means the row is excluded (neither TRUE nor FALSE)
Examples
NULL checks in WHERE
-- Find rows with no phone number
SELECT * FROM customers WHERE phone IS NULL;
-- Find rows that do have a phone number
SELECT * FROM customers WHERE phone IS NOT NULL;
-- Common mistake
SELECT * FROM customers WHERE phone = NULL; -- always returns 0 rows!NULL behavior in expressions
SELECT NULL + 5; -- NULL
SELECT NULL = NULL; -- NULL
SELECT NULL IS NULL; -- TRUE
SELECT NULL IS NOT NULL; -- FALSE
SELECT NULL OR TRUE; -- TRUE (special case)
SELECT NULL AND FALSE; -- FALSE (special case)Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4