NULL with NOT IN () caluse

Most of you must be aware of the fact that NULL value causes more problem than anything else database world. Well here is an example which may or already have ruined few minutes @m some point in your career.

NULL means nothing then Why on the earth should we care about it ? well it is one of the most important thing that DB developer should care or you'd be doomed for unexpected results.

Let's see one example of NULL causing few of us headache.. 

We are creating two table variables with some data to check the details

DECLARE @Source TABLE

(

ID INT

) 

INSERT INTO @Source

SELECT 1 UNION ALL

SELECT NULL UNION ALL

SELECT 2 

DECLARE @Desti TABLE

(

ID INT

) 

INSERT INTO @Desti

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT NULL

Done. Now tell me ,What will be output of the following SQL ?

SELECT * FROM @Source

WHERE ID IN (SELECT ID FROM @Desti) 

You got that write didn't you? Thinking two NULLs are not equal. The output was 2, right.

Now lets try to match NULLs

SET ANSI_NULLS OFF

SELECT * FROM @Source

WHERE ID IN (SELECT ID FROM @Desti) 

Well well you got NULL in the Output it is because now = operator worked with NULL. It is because you disabled the setting which states that NULL with any comparison operator would return NULL. 

The IN clause here suggest that if either of the value from the @desti matches with the value from @source then return that value

i.e. SQL query can be represented as 

SELECT * FROM @Source

WHERE (ID = 2 OR ID = 3 OR ID = NULL)

Then, try following statement now

SET ANSI_NULLS ON

SELECT * FROM @Source

WHERE ID NOT IN (SELECT ID FROM @Desti) 

You have enabled "that" setting again and changed the IN operator to NOT IN ... what does NOT IN clause tells us here .. Fetch value from @source which does not match with  any of  the value from @desti 

Now what did you get the o/p ? Did you think you'll get 1. If yes, then here after the post is for you but if you were right then you might be here to revise the concepts so either way please continue reading :)

It did not return anything. Well Why ? The SQL query can be rewritten as 

SELECT * FROM @Source

WHERE (ID <> 2 AND ID <> 3 AND ID <> NULL)

Please note the last condition in the ( ) ID <> NULL -- it will always return NULL (FALSE) with SET ANSI_NULLS ON.. and output of it is AND with previous conditions which negates everything thus result is returning no rows. 

Let us try following 

SET ANSI_NULLS OFF

SELECT * FROM @Source

WHERE ID NOT IN (SELECT ID FROM @Desti)

And now it returned the expected output when we avoided returning NULL (FALSE) value with comparison operator.

So please beware of this pitfall that you might run into when you have NULL values in the column that you are comparing with... In case you do not want to set ANSI_NULLS OFF then you might want to do following adjustment for desired result 

SET ANSI_NULLS ON

SELECT * FROM @Source

WHERE ID NOT IN (SELECT ID FROM @Desti WHERE ID IS NOT NULL) 

Now that you've understood the problem please be careful. And let me know the output for the following queries

DECLARE @TAB TABLE

(

ID INT

) 

INSERT INTO @TAB

SELECT 1 UNION ALL

SELECT NULL UNION ALL

SELECT 2

DECLARE @TAB1 TABLE

(

ID INT

) 

INSERT INTO @TAB1

SELECT 2 UNION ALL

SELECT 3 

SET ANSI_NULLS ON

SELECT * FROM @TAB

WHERE ID NOT IN (SELECT ID FROM @TAB1)

SET ANSI_NULLS OFF

SELECT * FROM @TAB

WHERE ID NOT IN (SELECT ID FROM @TAB1)

SET ANSI_NULLS ON

SELECT * FROM @TAB

WHERE ID NOT IN (SELECT ID FROM @TAB1) OR ID IS NULL