When you filter out a value using !=, it's easy to forget that NULL won't come along for the ride since it's the database's way of saying 'value unknown'. To ensure NULL values don't slip through the net, you've got to explicitly fish for them with IS NULL or broaden your WHERE clause to catch everything with OR Val IS NULL. It’s SQL’s subtle reminder that NULL requires special attention.
I don't find this behaviour crazy or unexpected at all.
NULL doesn't refer to any specific 'missing value' state, instead it represents a larger group of possible values of which we don't know the actual value.
So in a pile of values 'foo' and 'bar', NULL could mean either of them, so there is no way of knowing if a NULL row is foo or if it is bar. We can't even know if two NULLS are equal.
This reads like you have worked with SQL a lot and has a skewed perspective. As a newcomer to SQL, this could be very surprising given that this diverges from every language that Im aware of having nulls.
Which is why these comparisons should be errors, rather than producing gibberish results. Trinary logic stuffed into Boolean operations is just psychotic
Yeah, I agree. The focus here should be the principle of least surprise so your database works correctly. Throw an error to ensure that unintuitive edge cases are handled explicitly. This is SQL after all. It’s used for business purposes, not to demonstrate elegant type systems built around relational algebra.
> For every "unknown" value, the sgbd will NOT decide for you if it's true or false
But it did decide. It could either return the row, or not return the row - there's no middle ground. By not returning the row, it implied that null is not not 'crazy'.
If it actually wanted not to decide, it could return an error.
It is called Structured Query Language, it allows for complex logic that can be processed close to the data to speed up operations and reduce complexity and increase reliability of client applications.
Sure, if you require Google scale of connectivity, it might not work but 99.9999% of people do not and their applications would be simpler, cleaner, more performant and more reliable if they learned and used their database correctly instead of re-implementing all that functionality (but badly) in their application layer.