Friday, June 18, 2010

NULL is a four letter word

I used to be a big fan of NULL. It represents something that isn't really there and in many, many cases it makes sense logically and mathematically. I even had a good way of explaining it to lay-people that didn't really get it. think of it like bank accounts and account numbers for those bank accounts. An existing account with a balance of zero is different than not having an account at all, right? This worked for most people to help explain the difference, especially in text fields where the value of some fields would be the empty string and others would be NULL.

Lately though, I've come to the realization that this doesn't really matter. You can explain this in a multitude of ways that make sense, but when Judy in marketing wants to send an email to all of the people that are not in the Accounting department, she doesn't care about NULL. As the more technically or mathematically inclined among us know, if the department field for somebody is NULL, in most databases it would not be comparable to a string because it has no value to compare.

Select * from Employees where dept != 'Accounting'

will not return the people where dept is NULL in most cases. And this is usually very bad in the real world.

I'm done with NULL if anything close to a reasonable default exists. It just eliminates so many real world problems. I know from time to time I've come across people on discussion boards that have expressed this view and for the longest time I've disagreed with that notion on principle. I know many others that have as well. After much wailing and gnashing of teeth, I finally get it.

Consider it the 8th dirty word, after fuck, shit, piss, cunt, cocksucker, motherfucker and tits.

No comments: