Semantically that data IS NULL
, setting it to something else is awkward at best. We can always assume NULL is NULL, we know what that is. if you use ''
then all of a sudden you have to keep track of what you're setting as your new NULL. Just use the construct of the system where possible.
Using a value for a null has problems as well; a query will never match a null to anything; it's not a value so no range of options will ever match it; the exception is the IS NULL
condition, which has special semantic meaning as NULL
has special semantic meaning.
In addition you're dubiously exploiting the database's NOT NULL
constraint. That constraint is meant to mean that column always has data. Your data technically fulfills that constraint, but it does not semantically.
The only reason I can see for setting NOT NULL
and using a homebrewed "NULL" value is when an application is inserting it's own data and it has it's own value for NULL
. For instance PHP's NULL
when stored to a database column will appear as a value, not a SQL NULL
. This can be avoided with proper programming in the application however, and I would only consider the "fake NULL" solution if I had no control over the application that was inserting "fake nulls"-- at that point the battle is lost, and your solution becomes the most applicable.