LearnContact
Lesson 4115 min read

IS NULL & IS NOT NULL

Learn what NULL really means in MySQL, why equality checks fail against it, and how to correctly test for missing values.

Introduction

Every operator you have learned so far — BETWEEN, IN, LIKE — compares a column against some actual value. But real-world data is often incomplete: a student might not have entered an email address, or an order might not yet have a delivery date. MySQL represents this missing information with a special marker called NULL.

This lesson explains what NULL truly means, why the ordinary = operator cannot detect it, and how to correctly find missing values using IS NULL and IS NOT NULL.

What You Will Learn
  • What NULL actually represents, and what it is not.
  • Why comparing a column to NULL with = always returns no rows.
  • How to correctly check for missing values with IS NULL.
  • How to correctly check for present values with IS NOT NULL.

What is NULL?

NULL means the complete absence of a value. It is not zero, it is not an empty string '', and it is not the word "null" as text — it represents a field where no value was ever recorded at all.

Simple Definition
  • NULL is not the number 0 — 0 is a real, known value.
  • NULL is not an empty string '' — an empty string is still a value, just one with no characters.
  • NULL means "unknown" or "not recorded" — the value simply is not there.

Consider a students table with columns id, name, and email. Some students never provided an email address, so that column is left as NULL for them.

idnameemail
1Alexalex@mail.com
2Samsam@mail.com
3PriyaNULL
4JohnNULL
5Meerameera@mail.com

Why = NULL Never Works

It is tempting to write WHERE email = NULL to find rows with a missing email. This does not work, and it will never return a single row, no matter how much data is actually missing.

wrong_way.sql
-- This looks reasonable, but it is always wrong
SELECT name
FROM students
WHERE email = NULL;
Result
Empty set (0 rows)

The reason is that NULL represents an unknown value, so MySQL cannot say whether it is equal to anything — not even to another NULL. Comparing anything to NULL with = or != does not produce true or false; it produces UNKNOWN, and rows are only included when a condition evaluates to true. UNKNOWN is treated as "not a match," so the row is silently excluded.

Remember This

NULL can never be compared using = or !=. Any comparison involving NULL with these operators evaluates to UNKNOWN, not true or false, so it will never appear in a WHERE clause's matching rows.

IS NULL

To correctly check whether a column has no value, use IS NULL instead of = NULL.

column_name IS NULL

IS NULL is a dedicated test built specifically to detect missing values, and it behaves exactly as you would expect.

IS NOT NULL

To find rows where a value is present, rather than missing, use IS NOT NULL.

column_name IS NOT NULL

This is the direct opposite of IS NULL, returning every row that does have a real value stored in that column.

Example: Finding Missing Emails

Let us correctly find every student with no email on file, using the students table shown earlier.

missing_emails.sql
SELECT name, email
FROM students
WHERE email IS NULL;
Result
+-------+-------+
| name  | email |
+-------+-------+
| Priya | NULL  |
| John  | NULL  |
+-------+-------+

And to find the opposite — every student who does have an email on file:

SELECT name, email
FROM students
WHERE email IS NOT NULL;
Result
+-------+-----------------+
| name  | email           |
+-------+-----------------+
| Alex  | alex@mail.com   |
| Sam   | sam@mail.com    |
| Meera | meera@mail.com  |
+-------+-----------------+

Common Mistakes

Avoid These Mistakes
  • Writing WHERE email = NULL, which always returns zero rows regardless of how much data is missing.
  • Writing WHERE email != NULL to try to find non-missing values — this fails for the exact same reason and also returns zero rows.
  • Confusing NULL with an empty string '' or the number 0, which are real, known values.
  • Forgetting that NULL values need special handling in aggregate functions and joins, topics covered later in this course.

Best Practices

  • Always use IS NULL and IS NOT NULL to test for missing values — never = NULL or != NULL.
  • Design tables so only genuinely optional fields (like an email address) are allowed to be NULL.
  • Consider whether a default value might be more appropriate than allowing NULL, especially for numeric fields.
  • When in doubt about whether a query is silently skipping NULL rows, check the column explicitly with IS NULL.

Frequently Asked Questions

Is NULL the same as an empty string?

No. An empty string '' is a real, known value with zero characters. NULL means no value was recorded at all — the two are fundamentally different.

Is NULL the same as 0?

No. 0 is a real, known numeric value. NULL means the value is completely absent, not that it happens to equal zero.

Why does WHERE email = NULL return no rows even when emails are missing?

Because NULL cannot be compared with =. The comparison evaluates to UNKNOWN rather than true, so MySQL never includes that row in the results.

Can a column be prevented from ever storing NULL?

Yes, using a NOT NULL constraint when the column is created, a topic covered in the Constraints lesson earlier in this course.

Key Takeaways

  • NULL represents the complete absence of a value — it is not 0 and not an empty string.
  • = NULL and != NULL never work, because NULL cannot be compared using equality operators.
  • IS NULL correctly tests whether a column's value is missing.
  • IS NOT NULL correctly tests whether a column's value is present.

Summary

NULL is one of the most important and most misunderstood concepts in SQL. It represents missing data, and it requires the dedicated IS NULL and IS NOT NULL operators rather than ordinary equality checks.

In this lesson, you learned what NULL truly means, why = NULL silently fails, and how to correctly find both missing and present values. Next, you will start working with String Functions to transform and inspect text data.

Lesson 41 Completed
  • You understand that NULL means a completely missing value.
  • You know why = NULL and != NULL never work.
  • You can correctly find missing values with IS NULL.
  • You can correctly find present values with IS NOT NULL.
Next Lesson →

String Functions