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.
SELECT * FROM Foobar WHERE Val = NULL;
I'm admittedly still learning SQL, but I'm very surprised by this.
The query should be:
SELECT * FROM Foobar WHERE Val IS NULL;If no one would expect NULL != 'crazy' to be True.
That's just intuitive, Null is not the string 'crazy'.
The crazy thing is that this person was using DBs for 15 years. Have they only ever used != with non-null columns before? Hard to imagine.
You must not qualify a boolean question for a sgbd like Schroedinger's cat.
A boolean condition in Sql has 3 values : - true - false - unknown ( aka NULL)
For every "unknown" value, the sgbd will NOT decide for you if it's true or false, you have to decide, because it trust you to know what you're doing.
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.
Win-win?
How does it suggest we actually go about modeling missing values in the database?
I can think of a dozen ways to go about it and each one has its own tradeoffs and consequences.
When it comes down to it, is actually it worth it? Just so you don’t have to consider NULL scenarios for columns that are marked as nullable?
It seems like a silly overcorrection to me.