Wednesday, October 1, 2008

SQL - you can't compare NULL to anything

Here is a little mind-bender for any of you who are programmers who routinely work with SQL. Write a little code that checks to see if NULL equals NULL. It returns false. Now write code to see if NULL is not equal NULL. It will return false too. Now repeat with NULL and any value. NULL equals 42 returns false, NULL is not equal 42 returns false. * What in the world is going on here?

Here's what I found when I ran into this issue before. What we're seeing here is the impact of NULL being a mathematical concept. SQL is built on many mathematical concepts. The concept of NULL is an unknown value. Since NULL is an unknown value, it may be 42, it may be Fred. We simply don't know. So comparing anything at all to NULL (even comparing NULL to itself) will *ALWAYS* return false. This is why you need to specify IS NULL or IS NOT NULL if you want to see if something is or isn't NULL.

* I experienced this issue most recently working with Oracle, but it will work with any SQL database such as MySQL, PostgreSQL, and Microsoft SQL Server.

No comments: