Does not equal operator and is null question

Posted on 2007-10-11
Last Modified: 2009-09-07
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)"
Question by:IUFITS
    LVL 8

    Accepted Solution

    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')
    LVL 3

    Author Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Title # Comments Views Activity
    Sql Data via Excel--performance issues 2 38
    Dsum and Max 10 28
    SQL display column name with condition 3 30
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now