Comment by recursive
9 years ago
You could represent a path through a grid as a string using U=up, R=right, D=down, L=left. Then a solution to a maze could be 'UUUURRRRLRUULLD'. An empty string indicates a solution to a maze in 0 zero steps. A null string indicates that there is no solution.
I'll give you that, but it's a very contrived example. I'm trying to think more of real world problems.
Many people have mentioned the idea of keeping track of if someone doesn't have a middle name versus you don't know their middle name. That makes more sense as an example (although I'm not sure how critical that is).
Treating the empty string as NULL, as ORACLE does, is the sane option on real world problems. Having "" different from NULL creates a lot of problems and the advantages are minimal. As you say, in the rare cases that you must tell WHY it is null, it is better to have a separated Boolean field, than to store "" vs NULL and give a meaning to each one.
One of the first problems that you'll encounter if you use a DB with "" != NULL is at data display, because the natural display for NULL and "" are "an empty cell". Then you cannot tell if it is NULL or ""... then you must display something else for NULL... then...
You have no such problem if "" is NULL, an empty cell always means NULL
They're different, and they mean different things. NULL means "I don't know this", whereas "" means "I know this is empty".
Data display with NULL != "" is easy, do what SSMS does and italicise/otherwise highlight the NULL.
What problems does "" being different to NULL cause?
> Having "" different from NULL creates a lot of problems and the advantages are minimal.
My experience is the exact opposite.
I can think of one way to display the data... JSON.
I think this is a bad use of `NULL`.
You should instead use a list of all possible solutions, or a sublist of that list (if you only need one solution) because then the empty list can indicate there are no solutions.
If you then want to do something you can iterate over the list: This will protect you from "null pointer" exceptions, promote more functional programming, and is usually less code as well.
versus: