Jamie-2229 (12/26/2014)
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.
You should not compare [NOT] IN to joins. They have different functions (extra columns added to result set; extra rows added if there are duplicates in the inputs).
The correct comparison is between [NOT] IN and [NOT] EXISTS. Every [NOT] IN condition with a subquery can be rewritten as [NOT] EXISTS (the reverse is not true), without the perceived incorrect behaviour caused by NULL values in the subquery.
I only ever use [NOT] IN with a list of constants (obviously never containing a hardcoded NULL). For subqueries, I always use [NOT] EXISTS.