[rskar]

Fun factoid, VBA/VB6 dichotomizes between the "NULL" of value (keyword Null) versus the "null" of reference (keyword Nothing).

    Dim x
    x = Null
    
    Debug.Print "x", x
    Debug.Print "x = x", x = x
    Debug.Print "x <> x", x <> x
    Debug.Print "x = 0", x = 0
    Debug.Print "0 = x", 0 = x
    Debug.Print "0 + x", 0 + x
 

(Yep, all of the above result in Null.)

    Dim y
    y = Nothing
    Debug.Print "y", y

(That one results in an "Object variable not set" error.)

[perl4ever]

VBA seems to have null, empty, "" and nothing, all of which have different semantics. But only variants can be null or empty, I believe; if you have a typed variable like a Date or a String, you're out of luck.

And then, I was using either Power Automate or SharePoint/odata and it turned out "null" does not have the "normal" null semantics, and to test for null, you simply check if something is equal to it.

I think I remember from using SQL Server that there is a option that you can treat null either way.

Microsoft has a weird relationship with null.

On the other hand, I'm sure someone has complained about Oracle's idea that empty strings are null values. And once you think about it, SQL's handling of nulls is weirdly inconsistent, because the special handling of them doesn't always apply in aggregates.

Somewhere I had a book by C.J. Date/Hugh Darwen in which I remember a rant about how nulls are a terrible offense against the true relational model.

[rskar]

>But only variants can be null or empty...

That's true, only Variants can be Null (Null is a state of Variant); however, Empty is translated to whatever default value makes sense for some of the (non-Object, non-user-defined) data types:

    Dim s As String, d As Date, x As Single, y As Double, i As Integer
    s = Empty: d = Empty: x = Empty: y = Empty: i = Empty
    Debug.Print s, d, x, y, i

(The above compiles and runs, and - no surprise - numerics are made zero, and string made "".)

>I'm sure someone has complained about Oracle's idea that empty strings are null values.

I have certainly complained about that: I mean, what's not to love about conflating "this field intentionally left blank" with "no certain value could be obtained for this field"? /s

>...a rant about how nulls are a terrible offense against the true relational model...

By way of Wikipedia, found this: https://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-TheAskewWall-pri.... A fine read. I am left wondering about how OUTER JOINs would be handled without NULLs. I am now also wondering if it was OUTER JOINs that made NULLs seem necessary.

[perl4ever]

"I mean, what's not to love about conflating "this field intentionally left blank" with "no certain value could be obtained for this field"

Well, probably because Oracle was the first database I used a lot, it just seems natural to me and Microsoft's distinction between the empty string and null is annoying.

On the other hand, if I were to try to rationally defend Oracle's way of doing it, it would be something along these lines:

You can subdivide the concept of "this field doesn't have a normal value" into an infinite number of reasons. So if you're not going to have zero "null-like" options, and you're not going to have one, then where does it stop? When you have two, or three, or four, etc. that seems like you've gone down the wrong path no matter how good your intentions are. It vaguely reminds me of the "zero, one, infinity" rule.

[perl4ever]

"Empty is translated to whatever default value makes sense"

You can assign it, but then you've lost the distinction. Conversely, you can't tell that you haven't assigned anything by testing for Empty. With a Date, it's probably clear that if it equals zero it's uninitialized, but with an integer, not so much.