AI Hints
Homeโ€บSQL Tutorialโ€บRanges, Patterns and Missing Values
SQL ยท Lesson 15 of 126

Missing Values: NULL

The value that is not there.

โฑ 2 min readโ–ถ Video at 21:19๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Some customers never gave Helen their email address. So what's stored in the email column for them? Not a blank word, and not zero. It's a special marker called null, which means, we don't know.

Missing Values: NULL
From the video: Missing Values: NULL
  • Some emails are missing
  • Not blank, not zero
  • NULL means unknown

Find the missing

To find nulls, we use is null. Not equals. Is. Run it. Here are customers with no email. SQLite shows a null as an empty space in the table. Helen can't send newsletters to these people.

query.sqlsql
SELECT first_name, last_name, email
FROM customers
WHERE email IS NULL
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ last_name โ”‚ email โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Grace      โ”‚ Lopez     โ”‚       โ”‚
โ”‚ Hugo       โ”‚ Kim       โ”‚       โ”‚
โ”‚ Carmen     โ”‚ Patel     โ”‚       โ”‚
โ”‚ Tara       โ”‚ Garcia    โ”‚       โ”‚
โ”‚ Vera       โ”‚ Smith     โ”‚       โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The equals trap

Now watch what happens if we use equals null by mistake. Run it. Zero! But we know there are missing emails. Equals null is never true, because you can't say that an unknown thing equals another unknown thing.

query.sqlsql
SELECT COUNT(*)
FROM customers
WHERE email = NULL;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ COUNT(*) โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 0        โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

IS NULL is right

Use is null instead. Run it. Thirty one customers have no email. And to find the opposite, customers who do have an email, you'd write is not null.

query.sqlsql
SELECT COUNT(*)
FROM customers
WHERE email IS NULL;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ COUNT(*) โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 31       โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Missing cities

Your turn. Some customers didn't give their city. Show the first name, last name, and country of five of them. Pause and try. Here's one way. Where city is null, limit five.

โœ๏ธ Exercise: Missing cities

Show 5 customers whose city is missing

Show hint

Missing means IS NULL, not = NULL

Show solution
solution.sqlsql
SELECT first_name, last_name, country
FROM customers
WHERE city IS NULL
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ last_name โ”‚  country  โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Hugo       โ”‚ Kim       โ”‚ Australia โ”‚
โ”‚ Priya      โ”‚ Tanaka    โ”‚ Germany   โ”‚
โ”‚ Will       โ”‚ Brown     โ”‚ Germany   โ”‚
โ”‚ Jack       โ”‚ Costa     โ”‚ Australia โ”‚
โ”‚ Umar       โ”‚ Costa     โ”‚ Canada    โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Null means unknown. Find it with is null, never with equals. That completes your filtering toolkit. Next, putting results in order.

Key takeaways
  • NULL means unknown
  • Use IS NULL / IS NOT NULL
  • = NULL never works
Next up: Sorting with ORDER BY โ€” put results in order