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

Missing evidence

Level 6: Transfer it · Exercise 1 of 2

Same idea as Missing is not zero, new data.

The IMDb table's gross column holds box-office takings in dollars, and 169 of its thousand films have none on file. Find the films from 2010 onward with no gross on file and an imdb_score of at least 8.0. Return id, title, year, imdb_score and gross, ordered by id. Finish line: five columns, every gross blank.

Finish line: 18 rows · columns id, title, year, imdb_score, gross · in the order the question gives

Data: IMDb Movies.

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.