[mulmen]

It’s not.

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’.

[gigatexal]

How is that different than “anything”?

If I’m comparing a value of type date to a null I still think it works as it should if value is “unknown”. What greater insight or context do we have if it’s a small-int null?

[mulmen]

> How is that different than “anything”?

Because the possible values are known.

> What greater insight or context do we have if it’s a small-int null?

The insight is that null is not a value. It’s not a smallint or a boolean or a date. It’s the absence of a possible value. The only way to see a null as tri-state is to conflate it with a nullable boolean. This is an incorrect mental model which leads to confusion.

[rawgabbit]

Suppose you have table with two columns to represent a classroom’s exam scores. It has the columns student_id (varchar) and score (int).

If a student is sick and has not taken the exam, yes you could enter -99 to represent they did not take the test. But if you want to find the class average, you would have to do something like this:

select average(case when score =-99 then null else score end) as class_avg from …

Or you could have entered null to begin with.