“Is not the beginning of wisdom the words: ‘I do not know’?”
– Data, Star Trek: Next Generation: “Where Silence Has Lease“
If the beginning of wisdom is to realize what it is that we do not know. NULL, by its definition is this not knowing. We do not know what NULL is, this is why it is NULL.
What is this?
Null is not a number, or letter. It may not even be a character. Using Occam’s razor, things are either NULL or NOT NULL. Things that are NULL are completely unknown at the time they are NULL. And things that are NOT NULL are not completely unknown when they are NOT NULL.
I feel the hair splitting on my head, so let me explain one important point. Things that are NULL are not destined to stay that way. Where /dev/null/ is the black hole of output that we don’t need, NULL is not the data equivalent of a black hole. Things that are currently NULL may become NOT NULL at any time, as soon as we know something about them. Pretty much anything really.
Empty String Theory
Unlike string theory, which attempts to define the nature of the universe, the ‘empty string theory’ is that empty strings are the same thing as NULL. They are not. If they were NULL, we would not know that they were strings. This seems quite clear. NULL is NULL and an empty string is NOT NULL.
“But wait!” I hear a Database Developer cry. “I can create a field in my database that is a string, and allow it to be NULL.” And this is correct, but this is not a contradiction. When you allow a database field to be NULL you have allowed it to receive NULL as an input. But it may or may not give NULL as an output. Because we can put NULL into a variable, when we query for that variable, element, whatever, we may get NULL, or we may get an empty string or property defined value of that data type of the field, that just happens to be empty.
In final reply to this inquiry I must observe that NULL is not equal to anything. Any conditional statement where NULL is compared to something will be false. Where, “” represents an empty string. If one writes the statement, ["" == ""], or in English, “if an empty string is equal to an empty string,” it evaluates as a true statement. However if one writes the statement [NULL == NULL], or in English, “if NULL is equal to NULL,” it evaluates to a false statement. Since an empty string is equal to another empty string, but NULL is not equal to NULL, then NULL cannot be the same thing as an empty string.
Why NULL is important
Just as early civilizations had no concept of zero, early discourses of NULL struggle with it. Where we can argue if you have zero apples, is the same as having nothing. Yet it can be the absence of something that is known. Zero can be having none of something. But NULL is not even knowing what the something is that you don’t have.
An apple basket can contain 0 apples as a way to say, “Hey, this basket is for apples!” So is 0 greater than NULL. Because with null we don’t know what the basket is for. We may not even have a basket at all.
There are a great number of operations that we couldn’t do well or efficiently if we didn’t have NULL. So understanding what we are asking when we look for NULL is important. Whenever we have a search for things that don’t match something else, we are searching for NULL, even if the values themselves are not NULL. We do this a lot, and we couldn’t do it without NULL.