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:
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:
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.
