Module 10 Checking for Null Values (IS NULL & IS NOT NULL)

Module 10

Checking for Null Values (IS NULL & IS NOT NULL)

A step-by-step, hands-on SQL practice lesson — with a live query playground

SELECT * FROM customers WHERE phone IS NULL;

The first time a query of mine quietly returned zero rows for no obvious reason, the culprit was NULL. I had written WHERE phone = NULL, it looked perfectly reasonable, and MySQL didn't complain — it just gave me nothing back, every single time, no matter how many rows actually had a missing phone number. That was my introduction to one of SQL's quirkiest rules: NULL doesn't play by the same logic as everything else. This lesson is the explanation I wish I'd had that day.

Contents

1. Introduction
2. Requirements
3. Practice: Step by Step
4. Common Problems & Solutions
5. Conclusion
6. Homework & Quiz
7. Live SQL Playground
8. Related Lessons
9. FAQ

Introduction

What You'll Learn — and Why It Matters

My experience

Once I understood that NULL means "unknown" rather than "empty" or "zero," a lot of strange behavior I'd seen in earlier queries suddenly made sense — including a few of the NOT queries from Module 7 that quietly dropped rows I expected to see. IS NULL and IS NOT NULL are the two operators built specifically to deal with this, and honestly, I now check for them out of habit any time I'm filtering a column I know can have missing data.

🎯 Objective

By the end of this lesson, you will be able to:

• Explain what NULL actually represents in a database.
• Use IS NULL to find missing values, and IS NOT NULL to find rows that have a value.
• Understand why = NULL and != NULL never work as expected.
• Combine IS NULL / IS NOT NULL with AND, OR, and NOT from earlier modules.

⚠️ What to Avoid

Common mistakes

• Writing WHERE column = NULL — this is the mistake I made, and it silently returns nothing, every time.
• Treating NULL as the same thing as an empty string '' or the number 0 — they are not equivalent.
• Forgetting that aggregate functions like COUNT(column) skip NULL values entirely, which can make a table look smaller than it really is.

💡 Important to Know

Before you start

I explain it to myself this way: NULL isn't a value sitting in a cell — it's the absence of one. You can't ask "is the absence of a value equal to something?" with a regular equals sign, because there's nothing there to compare. That's exactly why SQL gives you two dedicated keywords, IS NULL and IS NOT NULL, instead of expecting = and != to handle it.

Requirements

What You'll Need Before Starting

Operating SystemWindows, macOS, or Linux — everything here works identically no matter which one you're on.

Database ServerMySQL installed and running. See How to Install MySQL if you haven't set it up yet.

Optional but recommendedXAMPP with phpMyAdmin, if you'd rather work visually than at the command line. See How to Install XAMPP.

Prior knowledgeComfortable with SELECT, WHERE, AND/OR, and ideally NOT from earlier modules, starting with Module 1.

Nothing needs to be installed to try the live playground further down this page — it runs directly in your browser.

Practice

Step-by-Step Practice

I'll use a small customer list for this one — it's realistic, since in almost every real database I've worked with, a "phone number" or "email" column ends up with a few missing entries sooner or later.

1 Open MySQL (CLI or phpMyAdmin)

Open a terminal and connect with the MySQL command-line client, or open phpMyAdmin at http://localhost/phpmyadmin/.

mysql -u root -p

The basic syntax patterns for this lesson look like this:

SELECT columns FROM table WHERE column IS NULL;
SELECT columns FROM table WHERE column IS NOT NULL;

2 Create a Database and Verify It

CREATE DATABASE module10_practice;
SHOW DATABASES;
USE module10_practice;

Now create a customers table and verify it — I always run DESCRIBE right after, just to confirm every column looks the way I expect before I put any data in it.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(80),
  phone VARCHAR(20),
  signup_year INT
);
SHOW TABLES;
DESCRIBE customers;

3 Insert Sample Data

Notice a few rows below are missing a phone number or an email — that's deliberate. In a real signup form, not everyone fills in every optional field, and this table reflects that.

INSERT INTO customers (id, name, email, phone, signup_year) VALUES
(1, 'Sreymom', 'sreymom@mail.com', '012345678', 2023),
(2, 'Bunthoeun', NULL, '098765432', 2024),
(3, 'Kanha', 'kanha@mail.com', NULL, 2022),
(4, 'Vireak', 'vireak@mail.com', '011222333', 2024),
(5, 'Ratana', NULL, NULL, 2023);

4 Find Rows with a Missing Value: IS NULL

This finds every customer who never provided a phone number.

SELECT name, phone FROM customers
WHERE phone IS NULL;

5 Find Rows That Have a Value: IS NOT NULL

The opposite check — every customer who did provide an email.

SELECT name, email FROM customers
WHERE email IS NOT NULL;

6 Proving Why = NULL Doesn't Work

Run this exactly as written. It will return zero rows, even though we know at least two customers have a NULL email — that's the whole lesson in one query.

SELECT name, email FROM customers
WHERE email = NULL;

7 Combining IS NULL with AND

Customers who signed up in 2023 and never gave a phone number.

SELECT name, signup_year, phone FROM customers
WHERE signup_year = 2023 AND phone IS NULL;

8 Combining IS NOT NULL with OR and NOT

This pulls in customers missing either an email or a phone number — the people your support team would probably want to follow up with.

SELECT name, email, phone FROM customers
WHERE NOT (email IS NOT NULL AND phone IS NOT NULL);

9 Combine with Alias and Sorting

Bringing together everything from this series so far — column selection, an alias, an IS NOT NULL filter, and sorting.

SELECT name AS customer_name, signup_year
FROM customers
WHERE email IS NOT NULL
ORDER BY signup_year DESC;

Troubleshooting

Common Problems and Solutions

Every one of these is a mistake I've made myself at some point — NULL has a way of catching people off guard even after they think they've understood it.

Problem 1: WHERE email = NULL always returns zero rows

I expected this to find customers with a missing email, and instead I got nothing back — even though I could see blank emails in the table.

SolutionNULL can't be compared with =, because there's no value to compare against. Use WHERE email IS NULL instead.

Problem 2: WHERE email != NULL doesn't find customers with an email either

Same idea in reverse — I wanted everyone who did have an email, and this returned nothing.

Solution!= has the same problem as = when paired with NULL. Use WHERE email IS NOT NULL instead.

Problem 3: NULL rows disappear from my NOT queries

This one connects back to Module 7 — I wrote WHERE NOT phone = '011222333', and rows with a NULL phone quietly vanished from the results.

SolutionA NULL comparison evaluates to "unknown," not false, so NOT has nothing to flip. If you want those rows included, add OR phone IS NULL explicitly.

Problem 4: I mixed up NULL with an empty string

I ran WHERE email IS NULL and it missed a row where the email column held '' (an empty string) rather than a true missing value.

SolutionNULL and '' are different things — one means "no value was ever recorded," the other means "an empty piece of text was recorded." If your data mixes the two, you may need WHERE email IS NULL OR email = ''.

Problem 5: COUNT(column) gave a smaller number than I expected

I ran SELECT COUNT(phone) FROM customers expecting the total row count, and got a smaller number instead.

SolutionCOUNT(column) only counts rows where that specific column is not NULL. If you want the total number of rows regardless of missing values, use COUNT(*) instead.

Conclusion

What You Should Take Away

Looking back, the concept behind NULL is genuinely simple — it just means "we don't know" — but the practical habits around it took me a little longer to build. You should now be comfortable using IS NULL and IS NOT NULL to find missing or present values, and you should instinctively reach for them instead of = or != whenever a column might have gaps in it.

In Module 11, we'll build on everything from this series so far by looking at the IN operator — a cleaner way to write long chains of OR conditions on the same column.

Homework

Homework & Quiz

📝 Homework Task

Using the customers table from this lesson, write your own query that:

• Selects name, email, and phone.
• Finds customers who are missing both an email and a phone number.
• Sorts the results by signup_year, oldest first.

Try writing it yourself first — then check your work against the live playground below.

🧪 Quiz: Test Your Understanding (10 Questions)

Pick one answer per question, then click Submit to see your score instantly.

1. What does NULL represent in a database?

The number zero An empty string A missing or unknown value A deleted row

2. Which query correctly finds rows where phone is missing?

WHERE phone = NULL WHERE phone IS NULL WHERE phone != NULL WHERE phone = ''

3. What does WHERE column = NULL return?

Always zero rows Every row with a NULL value A syntax error Every row in the table

4. Which operator finds rows where a column DOES have a value?

IS NULL = NULL != NULL IS NOT NULL

5. Is NULL the same thing as an empty string ''?

Yes, always Only for text columns No, they are different Only in phpMyAdmin

6. What does COUNT(phone) count?

Every row in the table Only rows where phone is not NULL Only rows where phone is NULL The number of columns

7. Why does WHERE NOT phone = '011222333' exclude NULL phone rows?

Because NULL comparisons are unknown, not false, so NOT can't flip them Because NOT deletes NULL rows Because NULL is treated as true It doesn't — they're always included

8. Which query finds customers missing both an email and a phone?

WHERE email IS NULL OR phone IS NULL WHERE email = '' AND phone = '' WHERE NOT email IS NOT NULL WHERE email IS NULL AND phone IS NULL

9. Can IS NULL be combined with AND and OR from earlier modules?

No, it must be used alone Only with AND, never OR Yes, just like any other condition Only inside ORDER BY

10. Which clause would you add to sort IS NOT NULL results by signup_year, oldest first?

ORDER BY signup_year DESC ORDER BY signup_year ASC GROUP BY signup_year SORT signup_year

0 / 10

Try It Live

🧪 Live SQL Playground

A real SQLite database running entirely in your browser (via WebAssembly) — nothing is sent to a server. Practice using IS NULL and IS NOT NULL on the customers table below, or pick an example query to get started.

customers id INTEGER PK
name TEXT
email TEXT (nullable)
phone TEXT (nullable)
signup_year INTEGER

— Load an example query — List all customers Missing phone (IS NULL) Has an email (IS NOT NULL) The trap: email = NULL (always empty) 2023 signups missing a phone Missing email OR phone

SELECT * FROM customers;

Loading SQL engine…

Run a query to see results here.

Related Lessons

Continue the Series

Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide)

How to Install XAMPP Step by Step for Beginners

How to Install MySQL Step by Step for Beginners

FAQ

Frequently Asked Questions

1. What is NULL, in plain terms?

It's SQL's way of saying "no value was recorded here" — not zero, not blank text, just genuinely unknown or missing.

2. Why doesn't WHERE column = NULL work?

Because = compares two known values, and NULL isn't a known value to compare against — the comparison itself produces "unknown," which SQL treats as not matching.

3. What should I use instead of = NULL?

Use IS NULL to find missing values, and IS NOT NULL to find rows that do have a value in that column.

4. Can I use IS NULL with AND and OR?

Yes — it behaves like any other condition and combines naturally with AND, OR, and NOT, following the same parentheses rules covered in earlier modules.

5. Is an empty string the same as NULL?

No. An empty string '' is a recorded value — it just happens to contain no characters — while NULL means no value was recorded at all. Depending on your data, you may need to check for both separately.

6. Why did COUNT(column) give me a smaller number than the total rows?

COUNT(column) only counts rows where that column is not NULL. If you want the total row count regardless of missing data, use COUNT(*) instead.

7. Do NULL values show up in NOT queries?

Not automatically. A NULL comparison evaluates to "unknown" rather than false, so NOT has nothing to reverse — if you want those rows included, add an explicit OR column IS NULL.

8. Can a primary key column contain NULL?

No — a PRIMARY KEY column requires a unique, non-NULL value for every row, so NULL only tends to appear in optional columns.

9. Does sorting with ORDER BY handle NULL values specially?

Yes, in most databases NULL values are grouped together during sorting, typically appearing first or last depending on the database system — it's worth checking your specific database's behavior if the order matters.

10. What's a good habit for working with nullable columns?

Whenever I'm filtering a column I know can be empty, I ask myself upfront whether I care about the NULL rows, and reach for IS NULL or IS NOT NULL immediately rather than defaulting to = or !=.

Part of the CodeMend SQL Module series. Finished Module 10? Move on to Module 11 for the IN operator.