Hi Friends,

A very common issue that I see on forums is that SQL Server NOT IN clause not working. Here is a quick repro:

Note: These are dummy tables just for a quick demo so please do not try to find the logic between the two tables šŸ™‚

I create a customers table with custid and custname columns. I insert three records in it.

Note that a NULL gets inserted in the third record for custname columns. This is intentional.

This is what the customers table looks like, note the NULL there.

sql_server_not_in_clause_not working

I create another table: Orders table with two columns, ordered and custname and insert three records.

Note that in the third order I insert a customer name ā€˜Cā€™ that does not exist in customers table. (I told you not to find the logic, just understand the technical issue)

This is what the Orders table looks like:

sql_server_not_in_clause_not working

Now my final query: Give me all the customer names from Orders table that do not exist in customers table.

And I get an empty result set.

sql_server_not_in_clause_not working

I was expecting ā€˜Cā€™ to be returned.

The technical issue is:

NOT IN clause in SQL Server is nothing but a series of NOT EQUAL TO. One of the values from the subquery is a NULL. The result set (custname from tbl_customers) contains A, B & NULL. Every value from the outer query is compared with every value from the inner query. And when the comparison is done with NULL, it returns UNKNOWN because NULL is an unknown value and anything compared with unknown can only result in unknown. And UNKNOWN in this case of Logical comparison is treated as FALSE and an empty result set is being returned.

Solution 1: Put an additional filter.

Solution 2: Use Left Join

Solution 3: Use NOT EXIST clause

Solution 4: Using EXCEPT operator

All the solutions give you this output:

sql_server_not_in_clause_not working

Note that EXCEPT operator behaves a little differently. If there were two instances of C, it will only show one instance. It applies a DISTINCT SORT and removes duplication.

Now, if you run all four of them and check their execution plans, the first three seem to perform equally well. However the EXCEPT solution is expensive because of the DISTINCT SORT.

sql_server_not_in_clause_not working_5

So choice is yours and what you expect from the resultset!

And thatā€™s why it is very important to know your data well. You should be knowing whether the target column allows NULL or is it NULLABLE or not šŸ™‚

Want to watch this in action? Watch here!

 

Amit Bansal
SQL MCM, SQL MVP
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Connect on LinkedIn | Google+
FB page | Know more about me

Free SQL video: sqlservr.exe file size mystery
Free SQL Video : SQL Server NOT IN clause not working