[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3003
  • Last Modified:

Does not equal operator and is null question

I have a SQL statement that does a count and has this in the where statement... my question is, why do I have to add the person_status_code is null to get the correct count?  I would have thought that person_status_code <> 'D' would have returned everything, including null values since they don't equal 'D'... but to get the correct count I also had to include the or person_status_code is null.  Can someone give me the reason why it behaves like this?  Below is the SQL statement in question that I'm refering to.

select count(distinct person_id) as person_count from person where primary_source_code in ('A', 'PU') and (person.person_status_code <> 'D' or person.person_status_code is null)"
0
IUFITS
Asked:
IUFITS
1 Solution
 
jamcosCommented:
I can't explain why Microsoft implemented it so that NULL doesn't match <> 'D'. But I can confirm that you are correct in needing to account for it. The way we've worked around this is that if something can be NULL, we use COALESCE to give it a 'default' value we can work with and compare. Generally, during schema design we avoid allowing NULL in places that it doesn't make sense and can bite us this way.

select count(distinct person_id) as person_count from person where primary_source_code in ('A', 'PU') and (COALESCE(person.person_status_code, 'X') <> 'D')
0
 
IUFITSAuthor Commented:
Good answer, thanks!  Typically I try to avoid nulls also but unfortunately I don't maintain this database.  That's a great use of Coalesce, I use it often in the Select section but for some reason never thought of using it in the Where section.  That's a great tip.  

Thanks for the quick response!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now