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.