Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  MichaelT 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. –  MichaelT 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

5 Answers 5

up vote 3 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
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! –  Ӎσᶎ Nov 12 '13 at 2:08

This seems to be a duplicate question... http://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.

share|improve this answer
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

There’s a design principle that I work by very closely.

When modeling anything in a computer use the the simplest data structure capable of representing the values you wish to hold. If you need to represent a complex value break it up into multiple simple structures and use only the simplest you need at any particular time.

(Simplest means fewest parts, rules, or values that can vary.)

As column types that allow null are more complex than those that do not, it is better to specify NOT NULL unless you have need for NULL in mind. Why is this? Because every additional avenue of possibility for how a piece of data is stored is one you will have to write code to handle if you want your code to be robust i.e. handle all possible values.

The design of arrays in PHP is a lovely illustration of the importance of this principle and what happens when it is ignored. In most languages there’s a small handful of collection types: lists, maps, hashes, sets, etc. each with specific properties different from the last i.e. each with their own simple rules. In PHP there are none of these, there are just PHP special brand of array, which in computer science terms are probably something along the lines of an ordered table of indexed key and value pairs. (If that sounds like three things all at once that’s because it is!) I guess they thought this was appealing because it seems like you don’t have to learn about all these different structures. However the problem is that in order to do all these things PHP arrays have to be quite a complex data structure with lots of different rules and capabilities and you have to use all of those rules and capabilities all of the time.

What is the result of this?

Have a look at the sort functions in the PHP manual. In a normal language there is a sort function for a list and hopefully a second function such as sortBy that allows you to specify a custom comparison function and that’s it. There’s no sort functions for a map or a hash or a set because sorting just doesn’t make sense for those structures. In PHP there are (excluding reverse sort functions) the following sort functions: sort, asort, uasort, uksort, usort, and ksort. All these different functions are necessary because PHP arrays can be sorted in more than one way. Which one do you need?

Another example. Take a simple PHP for loop:

function prnArray($array) {
   for ($i = 0; $i < count($array); $i++) {
      echo $array[$i];
   }
}

Seems harmless enough. Until you realize that you could provide an with string keys like this:

prnArray(['foo' => 1, 'bar' => 2]);

Which will generate two undefined index errors (for indexes 0 and 1) and not print the array at all. Oops!

These are all problems the come from having more rules or possibilities representable in data than you need at any particular time one time.

share|improve this answer
3  
I really don't see how does this answer the question. You don't mention three-valued logic even once and that's what the question is about. –  svick Nov 8 '13 at 2:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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