Today I learnt I've been thinking about SQL nulls wrongly all this time. Apparently the condition in a where clause evaluates to one of three (not two) values: true, false or null. At least this is the case in MySQL and Postgres.
Say we have an orders table with a nullable status column, and the table contains the following records:
| id | status | ...other columns |
|---|---|---|
| 1 | running | ... |
| 2 | completed | ... |
| 3 | null | ... |
Then this query:
select * from orders where status != 'completed'
returns just row 1, because that's the only row where status != 'completed' is true. The fact that row 2 is missing in the result isn't surprising. But row 3?
I used to think a condition like the one above would be true for row 3. It turns out the condition actually evaluates to null instead, because status is null in the row. Generally when you compare null with another value, the result of the comparison is also null, so a select statement won't pick such a row.
There's a nice, short article by PlanetScale about dealing with nulls in MySQL. I'd recommend reading it to learn more.