Subscribe to this blog
Briefcase
Print
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.
Posted by Shawn Melton on 13 October 2010
There was a lot of NULL in that one </sql humor> :)
Posted by Steve Jones on 13 October 2010
:), felt like I kept writing NULL over and over in there.
Posted by jcrawf02 on 13 October 2010
Always feels wrong when I'm updating a column to NULL values (mostly loading in text files), since you have to SET myColumn = NULL
Posted by Joe Stefanelli on 13 October 2010
A subtle variation on this popped up on stackoverflow today: stackoverflow.com/.../3924694
Posted by Jason Brimhall on 13 October 2010
Gotta love NULL - great stuff.
Posted by Ken Lee-263418 on 17 October 2010
I remember looking at a table with a field that had a lot of NULL values. I wanted to see all the records that were null, I put in "where field IS NULL" and got nothing. The field was nullable and I'm scratching my head. (What the H???) Well, the field is varchar... maybe?? Yep "where field = 'NULL'" worked fine and left me wondering about the SQL knowledge level of the development team, I'd just joined, have.
Posted by Ken Lee-263418 on 17 October 2010
Another good one. Their code was working fine and all of a sudden it started blowing up. A money field had been consistently under a grand. They were calling a procedure without naming any parameters(positional method). If their code had passed 1234.56 or '1,234.56' it would have worked fine. They had to send the last version without quotes, thereby sending too many parameters. I'm sure glad the last parameter didn't have a default value and they generally used the default.
Posted by Ken Lee-263418 on 17 October 2010
whoops make "and they generally used the default" = "so they couldn't generally use the default'