[dragonwriter]

An unknown measurement isn't a measurement value its a statement of (lack of) knowledge about a measurement, that doesn't tell you what the measurement is. Knowledge about a measurement is as different from the measurement as the measurement itself is from the thing measured.

Whether two unknown measurements are the same is unknown.

[SigmundA]

Whether two measurements of 25C are the same is unknown, these are just values recorded in a database. 25 is a value, null is a value.

The values in the db are the same in both cases which is what I would like my db language to deal with and not make assumptions about what that value actually means.

I see no value in treating null special when in comes to equality in a sql db, in fact it is a hinderance that it does so in my experience.

[shawnz]

The SQL null is a database-specific keyword and not something that's part of the domain of your measurements. If you want some kind of sentinel value for your measurements that doesn't have the "unknown" meaning of SQL null, then you should use your own sentinel value and not reuse null for that purpose

[SigmundA]

Sentinel values suck especially when the language already has a perfectly good one built in.

Is 0 for a temp measurement unknown sentinel or an actual measurement, how about 2,147,483,647 great probably not a measurement now its always included in greater than queries same with max negative with less than.

Null separates the value into its own distinct group and prevents it from being including in range queries due to it not being an actual numeric value while most languages still allow you to compare equality using standard operators.

Sum types would be great in sql but currently we get a union of the sql type and null, so null for sentinel values it is except for the having to using weird syntax to compare it.

[Dylan16807]

Null is not your value that the database is making assumptions about, it's the database's value that you are making assumptions about.

A real sum type would be nice, but when you're using null then you need to accept that null was not designed with your specific use case in mind.

[dragonwriter]

> Null separates the value

NULL is not a value.

NULL is a statement that a value is not available or unspecified reasons.

If you want a particular value where a query would return NULL, it's your job to replace the NULLs with the contextually-appropriate value, e.g., using COALESCE(), to provide it.

It's a convenience shortcut to allow more complicated data models to be rpersented in simpler table structures than a fully normalized NULL-free data model would require, and to provide information about missing data (which can be used with things like COALESCE, where appropriate) when a more complex data model is simplified into a resultset via a query with JOINS, etc.

[SigmundA]

>NULL is not a value.

I do not agree it is most certainly a value that is stored in the database.

>NULL is a statement that a value is not available or unspecified reasons.

Again I disagree, it is a value that denotes its value is of a different type than specified in the columns data type. The meaning of that value is for the user to decide. The system is literally storing a value that can be compared against using special equality syntax (IS NULL, IS DISTINCT FROM, etc).

The actual column definition is a sum type defining possible values in the column it is a constraint on the values:

columnA int null columnB int not null

If someone asks you what the value of a column in a result is when null do you say "I don't know" or do you say "null"?

For all these statements about what null means philosophically and the history about why it is treated the way it is in SQL there is little compelling argument to what value having the the equals operator always returns false when comparing nulls and instead one must use a separate syntax to properly compare null values for equality other than its for historical reasons and it changing it would be difficult.

This adds no value over typical programming languages where the normal equality operator can be used, its is weird and the source of confusion and even more bugs than your typical null handling creates.

I would prefer a database with fully fleshed out sum types rather than marking a column nullable, then a column could be marked as say a number + string + special sentinel type or whatever combination of types makes sense for my application, and if it that db did exist I am sure its equality operator would properly compare type and value to give a consistent binary result rather than the nonsense that is SQL null equality.

Your statements about using coalesce don't seem compelling to me, maybe I am a misunderstanding, to efficiently search for a value with a index you must use that value, I should be searching for with column IS NULL not Coalesce(column,[special value]) = [special value] which would be extremely inefficient.

Many languages have null coalescing operators and still use the standard equality operators for null. Coalesce to a special numerical value for a numerical column to represent a sentinel value is again a waste of time that again leads to strange greater than less than issues. Given a type system that allows nulls I would rather use IS NULL than coalesce that would be a further step backward but even better would be = NULL and get rid of the IS operator and its variants.