Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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.

Comments

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'

Leave a Comment

Please register or log in to leave a comment.