Module 00 · lesson 7
NULL: when there's no value
The founder added a Gift Card to the catalog. But which category does it belong to? It's not Electronics, not Home... none at all. So what do you put in the category column? Certainly not "0" and not an empty string.
When there's no value
Sometimes there simply is no value. A Gift Card doesn't belong to any category — so what do you write in category? That's exactly what NULL is for.
NULL ≠ zero ≠ empty text
NULL means "no value" or "unknown". It's easy to confuse, so remember the difference:
| Value | What it is |
|---|---|
0 | a specific number — zero |
'' | empty, but existing, text |
NULL | no value at all |
At GadgetHub the gift card has category = NULL — it simply has no category.
NULL is "contagious" in comparisons
The main trap: category = NULL doesn't work the way you'd expect — the result is neither true nor false, but "unknown". That's why NULL is tested with a special operator, IS NULL (more on this in the module about filtering):
SELECT <columns>
FROM <table>
WHERE <column> IS NULL;
See for yourself
The task has a query that finds the product with no category. Look at how NULL is shown in the result (usually blank).
Summary
NULL= a missing/unknown value, not zero and not an empty string.- You test for it with
IS NULL, not= NULL.