IS NULL & IS NOT NULL
In this page:
Finding Empty Fields
In databases, NULL represents missing or unknown data, not zero or an empty string. We cannot use the equals sign to check for NULL, since NULL never equals anything, even itself. Instead, we must use the special IS NULL operator to find these rows.
Example: Finding Empty Fields
SELECT * FROM users WHERE phone IS NULL;Finding Completed Fields
If you want to find rows where a column has actual data, use the IS NOT NULL operator. This is perfect for identifying completed fields or verified accounts, like users who have confirmed their email address.
Example: Finding Completed Fields
SELECT * FROM users WHERE phone IS NOT NULL;Working with Nulls in Calculations
Doing math with NULL values can make the whole result NULL, which silently breaks totals and averages. We use functions like IFNULL or COALESCE to replace NULL values with a default number before the calculation runs.
Example: Working with Nulls in Calculations
SELECT IFNULL(discount, 0) AS discount, COALESCE(discount, 0) AS discount2 FROM orders;Why Equals Null Does Not Work
Using the normal equals sign with NULL returns an empty result set, which trips up a lot of beginners writing WHERE column = NULL. This is because NULL is not a value, so it cannot be equal to anything, including another NULL. Always remember to use IS NULL instead.
Example: Why Equals Null Does Not Work
SELECT * FROM users WHERE phone = NULL; -- always returns empty
SELECT * FROM users WHERE phone IS NULL; -- correct wayCombining Null Checks with Filters
You can mix NULL checks with other search conditions in your WHERE clause, such as finding active users with a missing phone number. We use AND or OR to tie them together cleanly, just like any other filter condition.
Example: Combining Null Checks with Filters
SELECT * FROM users WHERE is_active = 1 AND phone IS NULL;Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: