Blog Post

Common SQL Server Mistakes – Equals NULL

,

One thing that I don’t see a lot, but it still happens with people new to SQL Server is the comparisons they’ll make with NULL values. Often those people new to T-SQL will write this:

select CustomerID, CustomerName
from Customers
where SalesRepID = NULL

The thought here is they are looking for those customers that don’t have a salesrep assigned. Or they might enclose the NULL in quotes, but this won’t work.

The correct way to do this is:

select CustomerID, CustomerName
from Customers
where SalesRepID Is NULL

Note the “Is NULL” that will correctly return those customers who have a NULL value stored in that column.

Why?

NULL is an unknown value. We just don’t know what value it is, so it’s not a variable in algebra like “x”. In algebra, x=x, but NULL != NULL. Since we don’t know what the value is, and since each row could potentially have a different value (remember every NULL’s value is unknown) we can’t expect any NULL to equal any other NULL.

NULL isn’t a placeholder like a blank or space, or even zero. It’s an unknown value, so equals (and not equals) does not apply. Instead you need to use “Is NULL” or “Is Not NULL” for your comparisons.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating