In my syntax why does the NOT IN statement not exclude NULL values? To me, if NULL does not exist in #GoodAnswers it should return the dv

Below is syntax, can someone help explain this to me?

Create Table #GoodAnswer
  answer1 varchar(100)
  ,dv varchar(10)

Insert Into #GoodAnswer Values 

Create Table #Data
  bbc varchar(100)
  ,answer varchar(100)

Insert Into #Data Values ('lal', NULL), ('raj', 'Orange'), ('enif', 'Zoo')

Select bbc
    , case 
        when answer not in (
            Select answer1 
            from #GoodAnswer
        ) then (
            Select answer1
            from #GoodAnswer 
            where dv = 'Yes'
        else answer 
        end as [Valid Answer]
FROM #Data

Drop table #GoodAnswer
Drop Table #Data

My data returned from this result-set is

| lal  | NULL         |
| raj  | Blue         |
| enif | Blue         |

I feel that instead of NULL being returned it should return Blue since that is the dv in the table


NULL is special. It is neither equal nor non-equal.

Given the same temp tables in your example, consider the results from these queries:

--returns 3 rows

--returns 2 rows
WHERE answer NOT IN (SELECT answer1 FROM #GoodAnswer);

--returns 0 rows
WHERE answer IN (SELECT answer1 FROM #GoodAnswer);

This is because the definition of NULL is that it is unknown. Since you don't know the value of it, you can't evaluate it's equality or inequality. Instead, you have to explicitly query for IS NULL or IS NOT NULL.

| improve this answer | |

You're getting the NULL from the else clause of your Case structure because NULL doesn't evaluate. NULL bypasses the when answer not in test.

Null values can be difficult to navigate. Most recently I've seen this article from Brent Ozar's team discussing NULL values and their effect on join logic and performance.

For most evaluations you can use ISNULL but keep in mind that may affect query performance.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.