Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Why does a WHERE clause on a nullable field not return the null records?

    I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed (correct me if i am wrong).

    That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?

    Code:
    create table #nullTest (
    name varchar(50)
    ,revenue int)
    
    INSERT INTO #nullTest
    Values ('Tim',100)
    ,('Andrew', 50)
    ,(null, 200)
    
    SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
    Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
    Code:
    SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The short of it is that NULL is not equal to anything, nor is it NOT equal to anything. So any comparison to NULL (except IS NULL) will return false, regardless of the operation.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by MCrowley View Post
    The short of it is that NULL is not equal to anything, nor is it NOT equal to anything. So any comparison to NULL (except IS NULL) will return false, regardless of the operation.
    Makes sense. What is the standard way to defensively program for this? Using ISNULL or COALESCE is WHERE clause? Add "OR [nullable field name] IS NULL" ?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would generally go with the OR option. Wrapping the column in a function like ISNULL will invalidate any indexes on that column. Google for SARG (Search ARGument), and you will get a bunch of articles about why that happens.

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by MCrowley View Post
    I would generally go with the OR option. Wrapping the column in a function like ISNULL will invalidate any indexes on that column. Google for SARG (Search ARGument), and you will get a bunch of articles about why that happens.
    thanks for the info!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •