Yep, you're right. So if you are using an "IN" or a "NOT IN" statement, not only is it faster to join than use "IN" or "NOT IN", make the null conditions in the ON statement safe for a JOIN.
For example, suppose you have a half million member branch of the military and your subquery is
(SELECT ID FROM WHERE sex='M') . Assume this is a non-clustered index on the sex column or perhaps uses an INCLUDE on the ID column.
Now you take and write your query:
Select * From TheLASTTenYearsOfServiceRecords
WHERE [USER_ID] NOT IN (SELECT ID FROM WHERE sex = 'M') Assume a half-million records, each user has at least 1000 records, and that for some reason, about 10,000 users have no sex defined aka, sex is NULL. Also, for some reason, there was a software glitch three years back and TheLASTTenYearsOfServiceRecords data is corrupt because it was copied over and a null was introduced into the index which now has a NOCHECK on the [USER_ID] foreignkey index.
Run the query. Roll through the 50 million or so records and watch it fall apart.
Instead, a more correct way to write this is might be:
SELECT * FROM TheLASTTenYearsOfServiceRecords A
So, my point, stay away from IN statements - there's likely to be traps due to NULLs and the possibility of corrupt data. I think we are saying the same thing Lynn. I just get wound up when people use an IN statement. They are ugly.