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 4 of 6

No calorie count on file

Level 4: Write it yourself · Exercise 1 of 1

Return name and category for the menu items with no calorie count on file, ordered by name. Finish line: two columns, every row a menu item whose calories are unknown.

Finish line: 6 rows · columns name, category · in the order the question gives

Data: Northshore Coffee.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

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