NULL in SQL: Why = NULL Never Works (IS NULL and COALESCE)
Equals null never finds the empty values in SQL. See why, use IS NULL and IS NOT NULL instead, spot how nulls hide from not-equal filters, and fill the gaps with COALESCE, on a sample online shop database.
Step by step
Missing values
- Why equals null fails. Some customers never gave us an email. You'd think equals null finds them. It never does. Let me show you why, and the two tools that really work.
- The customers table. We'll use the customers table. Each customer has an email and a city, but a few of those boxes were left empty.
The trap
- The trap. Here's the query most people try first. Where email equals null. Zero rows. No error, just nothing. But we know two customers have no email. So what went wrong?
SELECT first_name, last_name, email FROM customers WHERE email = NULL;
IS NULL
- IS NULL. Null isn't a value. It means unknown. And nothing equals unknown, not even another null. So swap equals for is null. There they are. Two customers with no email. Great for a data quality check.
IS NULL - IS NOT NULL. Need the opposite, like a mailing list? Add not. Is not null keeps every row that has a value. Thirty eight customers we can email. Thirty eight plus two is forty, every customer counted once.
IS NOT NULL
Hidden nulls
- Nulls hide. Nulls hide in other filters too. Let's find every customer who is not in Leeds. Not equal is a less than sign and a greater than sign. Thirty four. But five customers live in Leeds, so we expected thirty five. The customer with no city is missing, because unknown is never not equal either.
SELECT first_name, city FROM customers WHERE city <> 'Leeds';
COALESCE
- COALESCE. To fill the gaps in a report, use coalesce. It gives back the first value that isn't null. Here, no email or unknown when the box is empty. Eleven customers in the East. No more blank boxes. One row now says no email, and one says unknown.
SELECT first_name, COALESCE(email, 'No email') AS email, COALESCE(city, 'Unknown') AS city FROM customers WHERE region = 'East';
Recap
- Null in four rules. So, null means unknown, so equals null never matches. Use is null and is not null. Watch for rows that quietly drop out of not equal filters. And coalesce fills the gaps.
