NOT IN or IN Clause gives unexpected result Azure Synapse/Azure SQL Database

I have seen SQL developers using IN / NOT IN / NOT EXISTS conditions frequently in Azure Synapse/Azure SQL Database to filter out the rows which are not common in either of the tables using few joins and sub-queries. I made an observation recently that the NOT IN clause did not give expected number of rows for a query. 

For ex. If you expect an output of 3022 rows not in table T1 but the actual result you get is really unexpected ! Say you get an output result 0. Oops! How is it possible that you had to get an output of 3022 rows, but the actual windows shows you Zero result 

Expected :

Actual Output :

Question : Why there is a difference ?

Answer : This is a limitation of the command when you have NULL record value in your table. Yes you read it correct, this is limitation. Please read out below :

Demo :

Case 1 where Not IN gives expected result :

I introduced NULL values in one columns on which I have put NOT IN or IN join and see the difference in output just because of one Null Value :

How to Identify the issue:

Try to find if any of the columns have NULL values due to which the issue is being faced :

Mitigation :

1. You can use NOT EXISTS instead which will work even after these constraints (NULL valued column)

You need to use Syntax for NOT EXISTS :

2. You can eliminate NULLs. 

That's all Folks

Cheers! Don't forget to comment/Ask Questions!