Why it happens
In SQL's three-valued logic, NULL represents an unknown/missing value, not a normal value equal to itself. Any comparison with = or <> against NULL—including assignee = NULL and NULL = NULL—evaluates to UNKNOWN, and WHERE only returns rows where the condition evaluates to TRUE.
---
The Fix
Use IS NULL (or IS NOT NULL when checking for present values):
SELECT title
FROM tasks
WHERE assignee IS NULL
ORDER BY id;
(Note: When comparing two columns or parameters that might both be NULL in SQLite, you can also use IS—the standard SQL IS NOT DISTINCT FROM operator—e.g. WHERE assignee IS ?.)
This returns Fix login and Ship it.
🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: Fix login and Ship it.
SQL block 1 (SELECT title FROM tasks): ✅ matches the expected result
| title |
|---|
| Fix login |
| Ship it |
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.