SQL nulls are weird

I believe this confusion is confusing the tool with the thing being measured. For simplicity, I will use the analogy of a record (stored as a row in the database) as an observation in a scientific experiment. If the tool was able to record a value, I enter a value like 579.13. If the tool was not able to record a value, the tool will enter NULL. I make a total of one hundred observations. Of one hundred rows, some have values and some are NULL.

Are NULLs distinct values? No, they are simply a failure in measurement; it is like asking if all errors are distinct or the same. Are NULLS part of the same dataset? Yes, because they are all observations for the same scientific experiment. What does it mean when "select distinct ... " returns several rows for known/measurable values and but only one row for NULL? If this is confusing, the scientist can update the rows and substitute "UNKNOWN/ERROR" for every NULL. When you do "select distinct ...", you will get the same thing. It will return several rows for known/measurable values and but only one row for "UNKNOWN/ERROR".

Your link makes the same mistake I already addressed. It conflates nullable booleans with tri-state logic.

Null is not a value. It is the absence of a value.

> The SQL null value basically means “could be anything”.

This is wrong. Null means it could be any valid value but that value is unknown. If the datatype is DATE then the value cannot be boolean TRUE or the string ‘purple’.

But then if your sensor fails to record a measurement don't you end up with NULL for that row's temperature_id?

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

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ᶜ