Title = NULL

Why do people not understand the meaning of NULL, in databases?

It's simple. NULL is nothing. It is a complete absence of data. But this does not mean it is the same as a defined, but empty piece of data. That would not be NULL.

NULL is NULL. Except that it isn't. Nothing can be equal to NULL, not even NULL itself. Nothing can be compared to NULL, as nothing is equal to NULL. If you try and add NULL to a number, you get NULL. If you try to add NULL to a string of data, perhaps a name or address, you get NULL.

You've heard that computers deal in 1s and 0s - binary logic. This is true. All computer expressions are boolean, so they resolve to either true (1), or false (0). IF name="Neil" would resolve to true or false, depending on whether name did indeed contain the word "Neil". Except NULL. NULL is both true, false, true and false and neither true nor false all at the same time. It turns the 2-state logic of computers into a 3-state system. Consider this:

name = "Neil"
IF name = "Neil" <--- True

name = "Fred"
IF name = "Neil" <--- False

name = NULL
IF name = "Neil" <--- NULL - all your code dealing with true/false values will fail.

name = NULL
IF name = NULL <--- NULL. This will not return True, because NULL is not equal to NULL. Again, all your code dealing with true/false values will fail.

The reason for writing this is that I've just explained this for at least the 3rd time to a colleague who should probably know it by now. So, to set the record straight. To test for equality with NULL values in SQL (i.e. IF name = NULL) you need to use IS NULL.

name = NULL
IF name IS NULL <--- True. Hurrah! Everything works again!

Got it now?