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

Tea under 150 calories

Level 3: Predict the result · Exercise 1 of 1

The query below returns name, category and calories for the Tea items with fewer than 150 calories, ordered by name. Two teas, Green Tea and Earl Grey, have 0 calories on file. Before you run it, how many rows come back?

SELECT name, category, calories
FROM menu_items
WHERE category = 'Tea' AND calories < 150
ORDER BY name;

The query. Predict before you run it.

Data: Northshore Coffee.

I would like help with this exercise

Opens once your prediction is right, or after the worked answer.

Query results

The real result appears here after you run it.

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