0
Challenge · 09SQL I: Asking Questions of Data
NULL & three-valued logic
SQLused in ~90% of apps
Your DevLog feed has a rule: never show a blank card. If a post has no summary yet, render "No summary yet" instead of empty space. That sounds like a job for `WHERE body <> 'something'` or `= 'something'` — and both of those are exactly the queries that silently make posts vanish from a feed that should have shown them. NULL is not a value your filters can catch the normal way, and this is the concept where you learn why.
Core idea. NULL means "unknown / absent," not zero or an empty string — and it infects every comparison it touches: `col = 'x'`, `col <> 'x'`, `col > 5` all evaluate to NULL, not true or false, when `col` is NULL. A WHERE clause only keeps rows where the test is true, and NULL is not true — so a NULL row fails `= 'x'` AND its exact opposite `<> 'x'` at the same time, vanishing from both. `IS NULL` / `IS NOT NULL` are the only tests built to answer the question directly.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
| title | status | predicate | kept? |
|---|---|---|---|
| Why I switched | published | true | kept |
| RLS notes | draft | false | dropped |
| Half-written idea | NULL | NULL | dropped |
| Ships on Fridays | published | true | kept |
| Untitled | NULL | NULL | dropped |
returns2 rows
Two rows, not four. The NULL rows are missing. `NULL <> 'draft'` is NULL — unknown, not true — so they are filtered out. This is the bug that silently loses data.
The rule: a
WHERE clause keeps a row only when the predicate is true. NULL is not true and it is not false — it is unknown. So a row with a NULL fails = 'x' and <> 'x' at the same time, and quietly disappears from both.↑ play a little first, then dig in