Missing is not zero
IS NULL and IS NOT NULL in a filter
Northshore Coffee's menu has 45 items and six of them have no calorie count on file; three of its eight shops have no phone number.
Can you
- Find the rows with an unknown value using IS NULL, and say why = NULL finds none
- Predict which rows an ordinary comparison drops when a value is unknown
- Report how many rows are unknown instead of treating them as zero
The reading
Read the whole chapterMissing 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;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.
The ladder
Start at the first rung even if the idea looks familiar: it is answerable from the reading alone, and the climb is where the difficulty lives. A wrong answer sends you back to the paragraph that teaches it, then to a hint, then to the worked answer.
Learn
- Practise this rung
Level 1: Watch it answered
- Practise this rung
Level 2: Fill the blank
Practise
- Practise this rung
Level 3: Predict the result
- Practise this rung
Level 4: Write it yourself
Master
- Practise this rung
Level 5: Repair it
- Practise this rung
Level 6: Transfer it
Keep practising
The whole bank for this concept, in any order, with the same review, hint and worked answer.
Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.