← Back to MySQL Course | Chapter 6: Filtering & Sorting | Lesson 6 of 8

IS NULL & IS NOT NULL

NULL means 'we do not know', which is different from zero or blank. You ask about it with IS NULL, because an equals sign cannot compare a mystery.

In this page:

  1. Finding Empty Fields
  2. Finding Completed Fields
  3. Working with Nulls in Calculations
  4. Why Equals Null Does Not Work
  5. Combining Null Checks with Filters
Syntax
sql 
WHERE column_name IS NULL
WHERE column_name IS NOT NULL

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

sql 
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

sql 
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

sql 
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

sql 
SELECT * FROM users WHERE phone = NULL; -- always returns empty
SELECT * FROM users WHERE phone IS NULL; -- correct way

Combining 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

sql 
SELECT * FROM users WHERE is_active = 1 AND phone IS NULL;
Related Topics

NOT NULL Constraint · AND OR NOT Operators · EXISTS & NOT EXISTS · LIKE & Wildcards

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Writing WHERE phone = NULL, which never matches because NULL is compared with IS NULL.
  2. Treating an empty string '' as NULL, when they are different values.
  3. Adding a NULL column in arithmetic and expecting a number, when the result becomes NULL.
🔒

Chapter Quiz — Complete all 8 topics to unlock

0/8 topics done

Complete these topics first:

← Previous Lesson LIKE & Wildcards Next Lesson → ORDER BY

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.