NOT IN Not Working in SQL Server

This may sound a little silly for SQL experts, but can really help for someone working newly on SQL queries. The NOT IN query is used when there is a need to select the data on the first table, but not the matching records on the second. While selecting such data, there are chances of getting just the NULL in the output though there should be records for display.  

Let’s now take a simple example as below for a demonstration:

select ID from Table1
where ID not in (select ID from table2)

From the above query, we have an empty result. Most of the time there is confusion when there are millions of records and the NULLS are left unnoticed. Having NULLs in the columns for comparison can lead the results to be NULL.

Let’s try the same query by just adding IS NOT NULL for table2 in the WHERE clause.

select ID from Table1
where ID not in (select ID from table2 where ID is not null)

Here, we can see that the results are coming up for us. There are chances that we may forget or miss that thought that NULLs are present in either of the tables. So, always use the IS NOT NULL statement to fix the confusion of NOT IN not working.