Why NULL equals NULL is False

NULL has a special meaning. This was created to fulfill the need of having a character, that means ’nothing’ and it equals ’nothing’. So, it doesn’t even equal itself. Suppose, you have a column named ’PRICE’ in your table with datatype ‘money’. For a particular product, you don’t have data for price. How would you store it? Store a zero? But that would mean that the product is free!

Consider another scenario. You have a table, where you store the comments written by visitors on your site. Whenever a comment is written, you have to either approve it or disapprove it. If you disapprove, a ‘sorry’ mail is sent, if you approve, that comment is shown on your site, and a ‘thank you’ mail is sent. In this case, what would be the value of the column ‘approved’ initially?

Above two cases make a good case for storing a NULL.

Now the problem, how would you search for NULL in your query? The obvious answer would be to use something like:

select * from sometable where somecolumn=NULL

But above will NOT work. The reason is NULL is a special character and it equals nothing, not even it self. So, the solution to this problem is the IS NULL operator:

select * from sometable where somecolumn IS NULL

This works perfectly. Another solution is to set ANSI_NULLS to off:

This will work fine as well. The reason for this is compliance to SQL-92 standards. When you set ANSI_NULLS to off, you are actually turning off the compliance to SQL-92 standards. Read more about it here.

Happy Coding!