Blog

Blogs & News about e10 Infotech

Not IN Query for SQL : Strange Behaviour

While testing for one of my applications I figured that SQL 2008 for sure behaves very odd, Not sure if thats only for this version of problem with all of them.

Select * from Employee where EmployeeID 
not in (Select distinct ManagerID from Department)

0 Records Affected.Returned No Records. And I knew there are some records in Deparment table that doesn't have Manager set. ie ManagerID being Nullable. Now changing the query a bit does return all such rows

Select * from Employee where EmployeeID 
not in (Select distinct ManagerID from Department where ManagerID is not null)

25 Rows Affected. Strange ? Just good thing to know. I guess. Let me know your comments.

Add Comment

Your email address will not be published. Required fields are marked (*)