NULL and missing information
🏠The everyday picture
A form with a box left blank. Does blank mean "no", "not asked yet", "unknown" or "not applicable"? A blank box can mean many things, so good forms are clear about which.
📘What it really is
- NULL means "no value": unknown or not applicable. It isn't zero, and it isn't empty text.
- NULL behaves unusually:
NULL = NULLisn't true; it's unknown. Test withIS NULLandIS NOT NULL;- arithmetic with NULL gives NULL (
price + NULLis NULL); -
COUNT(column)skips NULLs;COUNT(*)counts all rows. -
Design choices:
- make columns
NOT NULLunless a missing value is genuinely possible and meaningful; - decide what a missing value means, and document it ("
closed_atis NULL while the enquiry is open"); -
avoid using fake values like
0,'N/A'or'1900-01-01'for "missing"; they get counted and averaged as real data. -
In TypeScript, a nullable column becomes
string | null, and the type checker makes you handle it (Module 27).
🖱️Try it
Add a nullable email to customers (not everyone gives one) and a NOT NULL phone. Insert customers with and without
email. Run SELECT COUNT(*), COUNT(email) FROM customers; and SELECT * FROM customers WHERE email = NULL; (returns
nothing) versus WHERE email IS NULL.
✅Quick check
Why does WHERE email = NULL find nothing?
Show the answer
NULL isn't equal to anything, even NULL; use IS NULL.
Why not store "no email" as the text 'N/A'?
Show the answer
It looks like a real value, gets counted and may even be emailed; NULL clearly means missing.