Re: c.d.theory glossary - NULL

Now you've done it! I can go on for hours about nothing at all! Now there's a subject I know something about!

First, we can't start a discussion of NULL without a passing reference to the curious incident of the dog in the night.

http://math.cofc.edu/faculty/kasman/MATHFICT/mfview.php?callnumber=mf383

Delightful.

Second, the NULL marker (bit, if you like) is far from the insanity bit. In most cases where it's used, it's more like the humility bit. The database doesn't know everything, and admitting that fact, where relevant, is better than making a random assertion of something for which there is no evidence. Some cells should have default values, but some should not.

Insisting that the database make an assertion about things it does not know is, itself, a form of insanity. I quoted Will Rogers in a different context: "It isn't the things we don't know that give us trouble. It's the things we know that ain't so."

Next, NULL is not a value. I don't care what Date or Kimball say on the subject. They don't know nothing.

Next, the absence of an assertion is not the assertion of an absence.

Next, every NULL in an RDB is the result of an outer join of relations. If I do an outer join between the employees and the domain of middle initials, the employees with no middle name will end up with a NULL for that attribute. Sometimes, even often, the base tables are designed with that outer join already performed, and materialized.

The fact that tables can have NULLs in them, and relations can't is another point of difference between tables and relations.

Oracle made a mistake, way down deep in the code, when it defined the NULL marker. A VARCHAR with length zero will be confused with a NULL. Try it. Set up a table with a VARCHAR column, and set the values to the empty string. You can express the empty string in SQL by two single quotes in a row. The test the value with ISNULL, and you'll see that it's NULL.

Assigning the empty string to a VARCHAR should be as different from setting a NULL flag as assigning the number zero to a NUMBER. Received on Sat Apr 24 2004 - 17:09:44 CEST