0

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.
Playground, this isn't a screenshot
titlestatuspredicatekept?
Why I switchedpublishedtruekept
RLS notesdraftfalsedropped
Half-written ideaNULLNULLdropped
Ships on Fridayspublishedtruekept
UntitledNULLNULLdropped
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