NULLs in SQL

In SQL, NULL is an indicator of the absence of value; consequently, the assertion that NULL = NULL cannot be parsed. To be evaluated, something must first have a value. I've certainly not explained anything here, I've merely proven that I understand the meaning and use of the English word "evaluate".

Imagination can be fairly described as visual thinking, and often when folks imagine NULL, they conjure images of empty strings or zeros; but both of these are values. Try imagining nothing. My favorite (though over-used) example of imagination versus non-visual thought is a Hectogon (a hundred-sided polygon). Try as you might, you cannot form a mental-image of this shape, but you can certainly think about it all the same.

Back to SQL. Consider the following simple table of Colors:

We allow the name column to be NULL, for 2 reasons:

1) we might not know the name of a given color or
2) perhaps this combination of red, green, and blue lacks a known name altogether

We cannot, however, allow the red, green, or blue columns to be NULL, for each of these is an essential bit of information used to define a Color. So we've tables which represent Entities, and those Entities have attributes some of which are necessary to define and describe the Entity in question. Basic database concepts. Consider this table of Tools:

Can you spot the error? Here's a hint: "Could you hand me a tool?" , "Sure, which tool?", "I don't know." "Well how about this one?" "I really don't know" and so it goes. The problem with this table is that we've allowed an essential attribute (the name of the Tool in question) to be NULL. I've seen lots of these awful tables in production we're-so-proud-of-our-flagship-product applications.

NULL and NOT NULL are important considerations, and the horrors continue when we find Tools with empty strings for names and NULL as well. This means that:

1) the client failed to require the obvious data
2) the middle-tier failed to check for the required data
3) the database allowed the bad data right in as well.

There are lots of defenses against bad data, foreign keys are great for this as well. Perhaps I'll add another crabby post about those.