"Let’s first take a look at what NULL is supposed to be. Here is the definition of NULL from the SQL-2003 standard: null value--A special value that is used to indicate the absence of any data value."
While the absence of a value may be represented by some value at the implementation level, I strongly recommend users not think of NULL as any kind of value at the model level. The problems with NULL stem precisely from the fact that it is not a value, but rather a marker for the absence of a value. NULL value is a contradiction in terms that distracts from the problems.
"... NULL does not mean “not applicable”. Of course, a NULL in a table is often a result of the attribute not being applicable for a specific occurrence of the entity stored in the table (e.g., a column “birthday” in a customer table that stores details of businesses as well as humans)--but in other columns and other tables, or even in another row of the same table, there might be a completely different reason for the data being missing (absent)!"Indeed. As I explained many times, the need for "inapplicable NULLs" is an artifact of poor design. If a property of a real world object "does not apply", the object does not have that property, so there should be no attribute representing the property for the tuple representing (the fact about) that object. The only reason NULL arises is because the design assigns an attribute to a tuple that does not have it--i.e., the logical model does not represent the conceptual model accurately . Nothing is missing!
"... NULL is also definitely not meant to signify “unknown”. Again, a NULL in a table might result from the value being unknown at data entry time ... but there might be other reasons as well. Unfortunately, many text books insist on explaining the behaviour of NULL in expressions by describing NULL as unknown, rather than missing or absent, causing this misconception about NULL to be the most widespread and the hardest to combat ... If the business doesn’t care why a birthday is missing, then the fact that there might be different causes doesn’t have any consequences on the database. If, on the other hand, the business does case about the reason ... then this reason should of course be modeled and stored in the database--but not in the same column ..."Couldn't agree more and I explicitly state this in , including how the DBMS could document the reasons in the database catalog. But it is important to be clear that while a value can be missing for any of a number of reasons, if it is missing, it is unknown to the DBMS.
"Things get more complex when NULLable columns are used in expressions and predicates. In a procedural language, this wouldn’t have been a problem--if a procedural program fails to find the information it needs, it enters a conditional branch to handle this situation, as defined by the programmer. In a declarative, set-based language such as SQL, this was not possible. The alternatives were either to have the SQL developer add conditional expressions for each nullable column in a query to handle missing data, or to define a decent default behavior in SQL for missing data so that developers only have to write explicit conditional expressions if they need to override the default behavior."While NULL default behavior may have been expedient for SQL implementers, they failed to consider the implications for the formal theoretical foundation of the RDM. The RDM is an adaptation of simple set theory (SST) and first order predicate logic (FOPL) for database management that works correctly only under two-valued logic (2VL) and the Closed World Assumption: in the real world a fact is unequivocally true or false, whether we know which is the case or not. If our knowledge of the world is imperfect, a database system based on any many-valued logic (nVL where n>2) will not yield results that are correct in the real world (in fact, efforts notwithstanding, no consistent nVL has been found for any n>2, and not for lack of trying ). nVLs make data language expressions complex and are unintuitive and prone to error due to realm confusion. Users think in 2VL, while SQL systems think in a (poorly implemented) 3VL. Worse, in practice NULL is used to represent both 'missing unknown' and 'inapplicable'--i.e., a 3VL is implemented for 4VL databases, exacerbating the problems: queries are not always the intended ones and results are easily misinterpreted (see next).
"The default NULL handling in expressions is very much based on how humans would handle similar situations ... if I refuse to specify my birthday, you would be unable to answer the question--so you wouldn’t answer it at all. And this is exactly what a database does--if any value to be used in an expression is missing (NULL), there won’t be any result of the expression; in other words, the result is missing (NULL) as well ... the rule of NULL propagation causes any expression that has a NULL in it to have no result, as if the other input values disappear as well ... the result being either true (row is included), false (row is omitted), or unknown (in which case the row is omitted as well)."Except that when a human says explicitly "I don't know" you can then probe to find what exactly he does and does not know and figure out the implications. A SQL system eliminates parts of the result--in fact, more than is necessary--without the user being aware of it, not to mention the query and integrity implications.
"Hey dude, I can’t answer that" is obviously not a valid value in any numeric, date, time, or datetime domain ... NULL however is valid, in any domain. So there really was no other choice but to return NULL if input data for an expression is missing ... This choice solves the problem, since we are now able to evaluate each predicate in a WHERE clause for each row..."Actually the validity of NULL in every domain obscures, rather than solves the problems, because the problems are not in data representation, but in data manipulation and integrity enforcement. Practitioners focused on the former are oblivious to the latter.
As we have demonstrated, these problems can be avoided by a 2VL, relational solution to missing data without NULL . It shifts the complexity burden from users to DBMS vendors, where it belongs. But, sadly, because they lack foundation knowledge, instead of demanding it, many data professionals defend NULLs, some ferociously. If you are one of those who ask, "If true relational systems are so great, why has nobody implemented one?", therein lies the the answer. Why bother if everybody believes that SQL systems are relational, which they are not and NULL is one reason why. 
See also: The worst mistake of computer science
 Pascal, F., Business Modeling for Database Design, PRACTICAL DATABASE FOUNDATIONS paper #3.
 Pascal, F., The Final NULL in the Coffin: A Relational Solution to Missing Data, PRACTICAL DATABASE FOUNDATIONS paper #6.
 McGoveran, D., Nothing From Nothing, Parts 1-4.
 Pascal, F., DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS: A DESK REFERENCE FOR THE THINKING DATA PROFESSIONAL (Database Debunkings, 2017).
Do you like this post? Please link back to this article by copying one of the codes below.URL: HTML link code: BB (forum) link code: