In SQL, NULL means unknown value. Thus, every comparison with NULL yields NULL (unknown) rather than TRUE or FALSE.From a conceptional point of view, this three-valued logic makes sense. From a practical point of view, every learner of SQL has, one time or another, made the classic WHERE myField = NULL mistake or learned the hard way that NOT IN does not do what one would expect when NULL values are present.It is my impression (please correct me if I am wrong) that the cases where this three-valued logic helps (e.g. WHERE myField IS NOT NULL AND myField <> 2 can be shortened to WHERE myField <> 2) are rare and, in those cases, people tend to use the longer version anyway for clarity, just like you would add a comment when using a clever, non-obvious hack.Is there some obvious advantage that I am missing? Or is there a general consensus among the development community that this has been a mistake?
In retrospect, has it been a good idea to use three-valued logic for SQL NULL comparisons?
database;sql;language design;null;logic
I think the crux of the problem is that as well as UNKNOWN it is also used to mean NOT APPLICABLE or ABSENT e.g. You have a PERSONS table with a SPOUSE_ID. What do you put in their for a single person? In most cases a designer will make this field NULLable to be filled with the partners ID when available and left blank for sad singles and happy bachelors.In my experience this is actually the most common use for NULLs. So while a comparison of two UNKNOWN values should result in another UNKNOWN; a comparison between two ABSENT values should result in equality -- but SQL does not allow for this.It would have been trivial to add another extra operator (say ==) to the mass of SQL keywords and operators which would indicate you want 2 nulls to be considered equal.While I think the relational model is sound and has a long future ahead, I think the mess that is SQL is due for a total rethink. It would be nice if we could start again from the very beginning and have an API based on Codd's original relational algebra.  