7

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?

  • PS: I know that this borders on "too broad" and "opinion-based", but I've tried to make it as answerable as possible. It is not meant as a rant, but as a genuine question. – Heinzi Nov 7 '13 at 22:08
  • 5
    A relational model for databases is based on relational algebra. Its not that its a mistake - its just the way the math under the system works. – user40980 Nov 7 '13 at 22:14
  • 2
    @MichaelT: If you expand on that, it could be a good answer to this question. – FrustratedWithFormsDesigner Nov 7 '13 at 22:26
  • @FrustratedWithFormsDesigner I'll think about it... while I have a reasonable grasp of SQL and have touched on relational algebra - the deeper implications of the math are lost on me. – user40980 Nov 7 '13 at 22:30
  • 1
    @svick "Are these two unknown values the same?" "I don't know, they're unknown." – Izkata Nov 12 '13 at 3:36
3

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.

1

The problem here is that NULL isn't a value--it's a nebulous set of values, and you don't know which one it is. Setting equality here is meaningless, because then the values wouldn't be NULL. It enforces the mathematical underpinnings of relational databases. In many ways, it's like asking why infinity = infinity isn't valid.

0

TRUE and FALSE make statements about the content of a value. NULL indicates the complete absence of any value at all. If NULL behaved the same way as FALSE, then you would be unable to account correctly for the absence of data in a query, something that is very important in a database. I think that alone makes NULL different enough from TRUE/FALSE that it merits being handled specially.

  • 4
    The question isn't “why aren't NULL and FALSE the same thing?”. It's basically “why was SQL designed so that myField = NULL doesn't work the way most people would expect?” – svick Nov 8 '13 at 2:10
  • 1
    @svick: a c programmer would expect that to return NULL and for myfield to be NULL afterwards. But since SQL was supposed to be understandable (and written) by random people with no programming experience, one could argue that NULL means "unknown" in the sense of "I don't know". Is this "I don't know" the same as that "I don't know"? I don't know! – Móż Nov 12 '13 at 2:08
0

This seems to be a duplicate question... https://stackoverflow.com/questions/7078837/why-doesnt-sql-support-null-instead-of-is-null

I don't think it's a mistake - this behavior is described in the ANSI standard.

Most databases allow you to change behavior of the equality operator.

set ansi_nulls on
if null = null
   print 'this will not print' 
set ansi_nulls off
if null = null  
   print 'this should print'

Also, you can note that many programming languages expect similar semantics when comparing objects against null.

  • 1
    What other programming languages have similar semantics? In all the other languages I know something == null works as expected, you don't need something like something is null in them. – svick Nov 8 '13 at 2:12
  • @svick: Python uses something is None because the meaning of something == None could be overridden by the type of something. – Greg Hewgill Nov 8 '13 at 3:34
  • @svick Also in Visual Basic you would use "is null" cf msdn.microsoft.com/en-us/library/… – Bruce H Nov 12 '13 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.