And I'm all for the idea that relational fields should be NOT NULL. I also fear that this doesn't really work for backwards compatible thinking. If I serialized some data down to disk before a field existed, I don't expect it to be there when I check it later.
You can be tempted to think it should just be the zero value of the type you are using. Or you can add some extra boilerplate around accessing. I think either works. Just make sure you aren't getting carried away. And, try to do anything that cares about the absence or presence of something at a layer from where you get that something. Don't punt the decision down your codebase.
(That is, Optionals are great at the layer, don't pass them as parameters to inner code, though. Obviously, YMMV. And, quite frankly, probably will go further than mine.)
What if the data is actually missing? How else do you record that information?
Having an extra field for "missingness" is less safe because the type system won't enforce that it is either missing or set, you could have it set to a value but marked as missing which is still ambiguous.
I concede there may be no difference in those meanings.
Conceptually NULL or nil is an appropriate concept for results that have no meaning, such as if an error occurred or if a passed value is not required or valid. (Though some structures can contain data that is 'incomplete' or 'not checked' and thus while a valid structure might not be 'validated' in the sense of conforming to a more specific set of expectations.)
field_is_set = true, field = "123"
field_is_set = false, field = 0 (some zero value or uninitialized value)
But isn't that a really clunky way of checking whether field is set? You can't just check field because the "zero value" could be a legit value, e.g. zero. So you have to first check field_is_set -- and now you have to make sure that's always correct and that nobody ever sets field by itself.
The question I ask in these circumstances: Will a method/function //always// return valid work if the program continues to run?
How about a 'find' function of some type? Find the nth thing, find matches of X, etc.
That's one type of function that might return no answer.
If a list or set of some sort is expected I'm happy with a zero-length list in this case. However lists aren't the only time this happens. The most recent example to come to my mind is finding the Nth item in an arbitrary sequence. That item might be out of bounds (not exist). Nil is appropriate for that case.
I avoid the word "empty" when referring to anything SQL related, as it is ambiguous in three value logic.
SELECT a.id, b.name FROM a LEFT JOIN b ON a.id = b.id
If you get a NULL in the name field, you don't know if that's because there's no record in b for that id, or if there is a record in b for that id but it has a NULL name value. Sometimes that difference will be important.