Course contentsShow
← Data Science
Lesson 861 of 2,14522. SQL FoundationsFree lesson

Filtering NULL Values

Use IS NULL and IS NOT NULL to handle missing data, since NULL != NULL in SQL.

Filtering NULL Values

You'll learn: How to correctly filter rows with missing data in SQL using IS NULL and IS NOT NULL.

Why NULL is Special in SQL

In SQL, NULL represents missing or unknown data—it's not zero, not an empty string, just absent. Here's the tricky part: NULL doesn't equal anything, not even itself. That means WHERE column = NULL will never work.

Think of NULL like a mystery box. You can't say one mystery box equals another mystery box—you don't know what's inside either one! SQL treats NULLs the same way.

The Correct Operators: IS NULL and IS NOT NULL

To check for missing values, SQL provides special operators:

-- Find all customers with no email address
SELECT customer_id, name
FROM customers
WHERE email IS NULL;

To find rows that do have a value:

-- Find customers who provided an email
SELECT customer_id, name, email
FROM customers
WHERE email IS NOT NULL;

Common Mistake to Avoid

This will not work:

WHERE email = NULL  -- Wrong! Returns no rows

Always use IS NULL or IS NOT NULL instead.

Practical Example

-- Get active orders missing a tracking number
SELECT order_id, customer_id, order_date
FROM orders
WHERE tracking_number IS NULL
  AND status = 'shipped';

This helps you identify data quality issues, like shipped orders that somehow lack tracking information.

Key Takeaway: Always use IS NULL to find missing values and IS NOT NULL to find present values—regular comparison operators like = and != won't work with NULL because NULL doesn't equal anything, not even itself.