| It treats the NULL/unknown value of the boolean as false 1 <> NULL => Boolean UNKNOWN, so SELECT * FROM foo WHERE 1 <> NULL returns nothing. 1 = NULL => Boolean UNKNOWN, so SELECT * FROM foo WHERE 1 = NULL returns nothing. That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself. > You're not doing `X = X -> false`, you're doing `X = UNKNOWN -> UNKNOWN` That's not how "=" works. If you want a relationship for testing equality than handles unknown, don't call it equality. Basic properties of equality, from Wikipedia https://en.wikipedia.org/wiki/Equality_(mathematics) - Reflexivity: for every a, one has a = a. - Symmetry: for every a and b, if a = b, then b = a. - Transitivity: for every a, b, and c, if a = b and b = c, then a = c. edit: We can also see the incoherence of this concept when we look at set theory. Because UNKONWN booleans are neither true or false, if you use them in a WHERE clause you get the ugly result that the set of X ⋃ Xᶜ
is not everything. |
I think the difference between how we're looking at this is for me there is no "value" of NULL. NULL has NO value which is why you can't do `1 = NULL` or `1 <> NULL` and have to specifically use `1 IS NOT NULL` or `1 IS DISTINCT FROM NULL`
>That's not how "=" works. If you want a relationship for testing equality >[that] handles unknown, don't call it equality. >Basic properties of equality, from Wikipedia >https://en.wikipedia.org/wiki/Equality_(mathematics) >- Reflexivity: for every a, one has a = a. >- Symmetry: for every a and b, if a = b, then b = a. >- Transitivity: for every a, b, and c, if a = b and b = c, then a = c.
Sure, that's all well and good, but equality only can work IF you know what the values are on either side. NULL means you don't know what a value is and therefore you can't answer whether or not a = NULL because NULL isn't known yet.
Or let me put it another way, is the launch price of the iPhone 17 != $799? The answer is neither true nor false, because the launch price of the iPhone 17 is unknown. We can make reasonable guesses, but if I give you a database with entries for iPhones from the first model and placeholder rows for the 17, 18, 19 and 20 models, none of them belong in the list when someone asks "which iPhones cost $799 at launch?" But equally true that none of them belong in the list when someone asks "which iPhones did not cost $799 at launch?"