Practise · Missing is not zero

Opening your saved work…

The reading

Missing is not zero

Rick's table has a number in every wordCount. Northshore Coffee's menu does not: the Chocolate Croissant has no calorie count on file, and neither do five other items. That blank is NULL, and NULL means unknown. It is not 0, it is not an empty piece of text, and it is not the word NULL. A croissant with an unknown calorie count is not a croissant with no calories.

Ask for calories = NULL and the engine returns no rows and no error. The comparison does not come out true or false; it comes out unknown, and WHERE keeps only rows whose test is true. The test that works is IS NULL, and its opposite is IS NOT NULL. The same goes for <> NULL, which keeps nothing either.

SELECT name, calories
FROM menu_items
WHERE calories IS NULL
ORDER BY name;

Six items with no calorie count. WHERE calories = NULL finds none of them. Open in the playground

An unknown value fails every ordinary test, in both directions. calories < 100 keeps 10 menu items, calories >= 100 keeps 29, and six items are in neither list: 10 plus 29 plus 6 is the whole menu of 45. BETWEEN and NOT BETWEEN split the same way. If a rule is supposed to include the unknowns, say so in the query: calories < 100 OR calories IS NULL.

Report the gap instead of filling it. A film with no gross on file did not earn nothing; 169 of the thousand IMDb films have no gross at all, and a report that quietly leaves them out, or quietly counts them as zero, is wrong twice. Say how many rows are unknown, next to the number you computed from the rest.

Rule of thumb. Unknown is not zero: test for it with IS NULL, and say how many rows are unknown.

Going deeper (optional, not on the exam)

NOT turns unknown into unknown, so NOT (calories < 100) keeps the same 29 items that calories >= 100 keeps and still skips the six. NOT IN with a NULL inside the list is worse: it can keep nothing at all. Chapter 8 returns to that when it builds sets of ids.

COALESCE can print a fallback in place of a blank. Replacing an unknown calorie count with 0 makes a claim about the croissant that nobody checked. Chapter 4 shows where a fallback is honest and where it is not.

Missing is not zero · IS NULL and IS NOT NULL in a filter · level 1 of 6

Find an evidence gap

Level 1: Watch it answered · Exercise 1 of 2

The IMDb table holds a thousand films, one row per film, and meta_score is the critics' Metascore. Which films from 2010 onward have no Metascore on file? Watch the query ask for the gap, then run it.

Data: IMDb Movies.

Why it works, clause by clause

WHERE meta_score IS NULL: keep the films whose score is unknown. = NULL would keep none of them.
AND year >= 2010: the second test, on the year.
SELECT id, title, year, meta_score: meta_score stays in the result on purpose, so you can see the blanks.
ORDER BY id: the same list every run.

From the reading: Missing is not zero, paragraph 2

I would like help with this exercise

SELECT id, title, year, meta_score
FROM movies
WHERE meta_score IS NULL AND year >= 2010
ORDER BY id;

Open in the playground

Query results

Run it to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.