Thinking more about null, I seems to me that the primary problem with nulls is that the theoretical definition of null is very different than the pragmatic use of null.
We all learned in DB101 that Null means "unknown" and this is why Null = Null is false.
In practice however, there’s nothing unknown about Null at all.
Nulls primarily show up in two places: Nullable Columns, and the result of Left Outer Joins for rows without a match.
In nullable columns a null doesn’t mean “unknown”. We know there’s no value yet entered for the column. Within the “closed system” of the database it’s an empty value. The value of a null in this case is that we know nothing has been entered. In contrast, a surrogate null value of zero or an empty string can be confusing because real data is indicating nothing has been entered. Does it mean zero, or has the value not yet been entered?
A left outer join between customers and orders returns a null OrderID for any customers without an order. Does this mean we have no idea how many orders the customer has placed? No clue at all if the customer has 0, 1, or 500 orders? Not at all. We know exactly how many orders the customer has placed: Zero. It’s a known value. Null means empty.
The problem with null is the inconsistency between the theory-based tests of equality and the practical use of null. SQL is based on the theoretical definition of null - unknown, while database developers actually use null to indicate empty.
For this reason, we'll never resolve the null debate until the industry accepts null as empty, or it creates an empty flag. Until then the best workaround is to forget the theoretical definition of null and accept that in practice, null means the known value of empty.