Some random IN vs. NOT IN with T-SQL
I'll try to expand on this, but anyway, here's some examples about how you have to be careful with IN and NOT IN in T-SQL in terms of when results will be returned. If the list you're comparing to has nulls, watch out. If the item you're comparing has nulls, watch out. If the list could be empty, check for that. IN and NOT IN may behave differently than you expect in all these situations. Below are most of the iterations. --in select 'row' where 1 in ( 1 , 2 , 3 ) --returns select 'row' where 1 in ( select 1 union select 2 ) --returns select 'row' where 1 in ( select null union select 2 ) --does NOT return - cannot determine if 1 "in" null, maybe it is, who knows? select 'row' where 1 in ( select top 0 f1 from ( select 1 f1 union select 2 ) qry ) --NO return b/c the top 0 qry returns "nothing", and 1 is NOT "in" nothing ("in nothing" = false) --so