How Dangerous Null values are in Sub query...

Recently I came across a strange problem while using simple sub query, everything was right but no data was returning.

DECLARE @Users TABLE (UserName VARCHAR(10))

DECLARE @ExcludeUsers TABLE (UserName Varchar(10))

INSERT INTO @Users

      SELECT 'User1' UNION SELECT 'User2'

     

INSERT INTO @ExcludeUsers

      SELECT 'User1' UNION SELECT NULL

SELECT * FROM @Users

SELECT * FROM @ExcludeUsers

SELECT * FROM @Users WHERE UserName NOT IN (SELECT UserName FROM @ExcludeUsers)

Here table1 (@Users) contains  two users

And table2  (@ExcludeUsers) contains one user and one NULL value

Now when we try to query the users that are not available in table2 (@ExcludeUsers) we find no rows due to one NULL value exists in table2 (@ExcludeUsers).

I have read on MSDN and found the reason as,

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.

So Any compare against NULL is always FALSE so we need to use IS NULL or IS NOT NULL.

 Happy Programming :)