Imagine you have a table tracking when a notification was last fired for each user.
You write a scope that finds rows where last_fired_on is not equal to today's date.
$query->whereDate('last_fired_on', '!=', $date);
Your intention with this simple query is to catch both rows where the date is different and rows where the date is NULL.
Maybe the rationale is that NULL is clearly not equal to any particular date, so that row should appear in the results.
But this is not how it works.
Instead, the query omits every row where last_fired_on is NULL.
This behavior isn't a quirk of a particular database engine, but part of the SQL standard.
By design, the SQL standard defines NULL as an "unknown" value, and any other value you compare to it also results in that same "unknown" value.
Even comparing NULL to NULL evaluates to unknown.
Within a query, the WHERE clause only keeps rows where the logic operator returns a true value, so the unknown rows are filtered out by design.
To include those rows on purpose, you have to ask for them explicitly.
$query->whereNull('last_fired_on')
->orWhereDate('last_fired_on', '!=', $date);
My takeaway rule is that whenever a nullable column is used in comparison logic, it's important to pause and decide what should happen to the NULL rows.
And if you want those NULL rows included, make that explicit with an additional whereNull clause.
Here to help,
Joel
P.S. Subtle bugs like this hide until production data surfaces them. A solid testing practice exercises the edge cases before they ship, which is exactly what I teach in my testing workshop.
