[tpmoney]

> And in many contexts in SQL, NULL is treated as equivalent to false, such as within a WHERE clause.

I don't think any databases treat `NULL` as `FALSE` in the WHERE clause. `SELECT * FROM foo WHERE bar = NULL` doesn't return rows with a NULL in the bar column. `SELECT * FROM foo WHERE bar != NULL` doesn't return rows without NULL in the bar column. `SELECT * FROM foo WHERE (bar = 'a') = NULL;` doesn't return rows where bar is not equal to `a`[1]. As far as I know every DB treats NULL as what it is, an unknown value.

It also doesn't to my mind violate the reflexive property because NULL is not equal to anything. It is a marker for an unknown value, not a value in and of itself. If you have a database of every person in a room and what color shirt they're wearing, and in your database, Alice and Bob both have NULL in their "shirt_color" column, that does not mean that Alice and Bob have the same color shirt. Nor does it mean that they don't have the same color shirt. Nor does it mean that someone with a green colored shirt has the same color shirt as Bob or Alice. It doesn't mean they don't have a shirt either. It means you don't have a record of/don't know what color their shirts are. You can't violate the reflexive property because you can't say what color shirt they have. You're not doing `X = X -> false`, you're doing `X = UNKNOWN -> UNKNOWN`

[1]: https://www.db-fiddle.com/f/iVDDRJos1pUqxnuy1jTEEe/0

[Pxtl]

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.

[tpmoney]

> That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself.

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?"

[empthought]

> That's the thing that's being treated as FALSE. That UNKNOWN. Not the value of NULL itself.

No, it's being treated as UNKNOWN, and the semantics of SELECT...WHERE only returns rows where the value of the condition is TRUE.

I think you need to look into https://en.wikipedia.org/wiki/Negation_as_failure