2 three-valued logic += http://en.wikipedia.org/wiki/Three-valued_logic "what's this?" edited Aug 8 '12 at 9:25 gnat 21.5k1616 gold badges8787 silver badges223223 bronze badges Unfortunately, Oracle confused the representation of VARCHAR string of length zero with the representation of NULL. They are both represented internally by a single byte with value zero. This makes the discussion just that much harder. A lot of the confusion surrounding NULL centers around three valued logicthree-valued logic. Consider the following pseudocode: ``````if ZIPCODE = NULL print "ZIPCODE is NULL" else if ZIPCODE <> NULL print "ZIPCODE is not NULL" else print "Something unknown has happened" `````` You wouldn't expect the third message, but that's what you would get, under three valued logic. Three valued logic leads people towards numerous bugs. Another source of confusion is drawing inferences from the absence of data, like drawing an inference from the dog that didn't bark in the night. Often these inferences were not what the writer of the NULL intended to cnvey. Having said that, there are plenty of situations where NULL handles the absence of data just fine, and produces exactly the results you want. One example is foreign keys in optional relationships. If you use a NULL to indicate no relationship in a given row, that row will drop out of an inner join, just as you would expect. Also, be aware that even if you avoid NULLS completely in the stored data (sixth normal form), if you do any outer joins, you are still going to have to cope with NULLS. Unfortunately, Oracle confused the representation of VARCHAR string of length zero with the representation of NULL. They are both represented internally by a single byte with value zero. This makes the discussion just that much harder. A lot of the confusion surrounding NULL centers around three valued logic. Consider the following pseudocode: ``````if ZIPCODE = NULL print "ZIPCODE is NULL" else if ZIPCODE <> NULL print "ZIPCODE is not NULL" else print "Something unknown has happened" `````` You wouldn't expect the third message, but that's what you would get, under three valued logic. Three valued logic leads people towards numerous bugs. Another source of confusion is drawing inferences from the absence of data, like drawing an inference from the dog that didn't bark in the night. Often these inferences were not what the writer of the NULL intended to cnvey. Having said that, there are plenty of situations where NULL handles the absence of data just fine, and produces exactly the results you want. One example is foreign keys in optional relationships. If you use a NULL to indicate no relationship in a given row, that row will drop out of an inner join, just as you would expect. Also, be aware that even if you avoid NULLS completely in the stored data (sixth normal form), if you do any outer joins, you are still going to have to cope with NULLS. Unfortunately, Oracle confused the representation of VARCHAR string of length zero with the representation of NULL. They are both represented internally by a single byte with value zero. This makes the discussion just that much harder. A lot of the confusion surrounding NULL centers around three-valued logic. Consider the following pseudocode: ``````if ZIPCODE = NULL print "ZIPCODE is NULL" else if ZIPCODE <> NULL print "ZIPCODE is not NULL" else print "Something unknown has happened" `````` You wouldn't expect the third message, but that's what you would get, under three valued logic. Three valued logic leads people towards numerous bugs. Another source of confusion is drawing inferences from the absence of data, like drawing an inference from the dog that didn't bark in the night. Often these inferences were not what the writer of the NULL intended to cnvey. Having said that, there are plenty of situations where NULL handles the absence of data just fine, and produces exactly the results you want. One example is foreign keys in optional relationships. If you use a NULL to indicate no relationship in a given row, that row will drop out of an inner join, just as you would expect. Also, be aware that even if you avoid NULLS completely in the stored data (sixth normal form), if you do any outer joins, you are still going to have to cope with NULLS. 1 answered Mar 18 '11 at 4:16 Walter Mitty 75644 silver badges1414 bronze badges Unfortunately, Oracle confused the representation of VARCHAR string of length zero with the representation of NULL. They are both represented internally by a single byte with value zero. This makes the discussion just that much harder. A lot of the confusion surrounding NULL centers around three valued logic. Consider the following pseudocode: ``````if ZIPCODE = NULL print "ZIPCODE is NULL" else if ZIPCODE <> NULL print "ZIPCODE is not NULL" else print "Something unknown has happened" `````` You wouldn't expect the third message, but that's what you would get, under three valued logic. Three valued logic leads people towards numerous bugs. Another source of confusion is drawing inferences from the absence of data, like drawing an inference from the dog that didn't bark in the night. Often these inferences were not what the writer of the NULL intended to cnvey. Having said that, there are plenty of situations where NULL handles the absence of data just fine, and produces exactly the results you want. One example is foreign keys in optional relationships. If you use a NULL to indicate no relationship in a given row, that row will drop out of an inner join, just as you would expect. Also, be aware that even if you avoid NULLS completely in the stored data (sixth normal form), if you do any outer joins, you are still going to have to cope with NULLS.