As you may or may not know, it is possible to use LEFT JOIN (and thus RIGHT JOIN) to filter out tables, just as it is with the NOT IN.
However, how do they perform against each other?
Well, using similar data as created in the "In versus INNER JOIN" example, I made two simple queries to see:

SELECT *
FROM TestOne
WHERE ID1 NOT IN
(
 SELECT ID1 FROM TestTwo
)
GO

SELECT T1.*
FROM TestOne T1
LEFT JOIN TestTwo T2 ON T1.ID1 = T2.ID1
WHERE T2.ID1 IS NULL
GO 

This gave the following execution plan:
(click to enlarge)

As can be seen the margin of difference - 46% versus 54% - is much smaller then with the IN syntax as linked above.
So I tried more complex queries where I just took the ones I made for the In versus INNER JOIN article, and modified them to be NOT IN and LEFT JOINS. Like this:


SELECT T1.*
FROM TestOne T1
WHERE T1.ID1 NOT IN (
  SELECT ID1 FROM TestTwo T2
  WHERE ID2 NOT IN (
   SELECT ID2 FROM TestFour T4
   WHERE ID4 NOT IN (
    SELECT ID4 FROM TestFive T5
   )
  )
 )
GO

SELECT T1.*
FROM TestOne T1
LEFT JOIN TestTwo T2 ON T1.ID1 = T2.ID1
LEFT JOIN TestFour T4 ON T2.ID2 = T4.ID2
LEFT JOIN TestFive T5 ON T4.ID4 = T5.ID4
WHERE T2.ID1 IS NULL
AND T4.ID2 IS NULL
AND T5.ID4 IS NULL
GO 

This gave a queryplan where the LEFT JOIN actually performened better, not by much but still 52 versus 48%. (I've minimized the query plans because I was only interested in the numbers, and not in the execution plan itself.)
(click to enlarge)

So where the case was more in favore of using IN instead of INNER JOINs when filtering inclusive, then exclusive looks to be more evenly matched when it comes to NOT IN versus the LEFT JOIN syntax.
But as always, use a case by case decision, by looking at your own querys details. This is just an example and guideline numbers.

Watch those join clauses.

One of the statements I’ve heard a couple of times over the last couple of years, is that it doesn’t matter where you place your additional on-clauses when joining, meaning either in “on” or in “where”. Well it does, depending on what you want to extract of information from your database. Performance wise and result wise.

An easy way to show this is to take two tables.
Suppose I have table (“TableOne”) consisting of a TableOnePK (primary key) and a value, and a table two “TableTwo” consisting of TableOneFK (foreign key) and a value.

If I then do a left join like:

SELECT *
FROM TableOne T1
LEFT JOIN TableTwo T2 on T1.TableOnePK = T2.TableOneFK AND T2.TableTwoValue = <target value>

This will get you all the rows from TableOne with nulls for the left join where it didn’t match anything in TableTwo. Pretty much as you would expect.

Now change the statement to

SELECT *
FROM TableOne T1
LEFT JOIN TableTwo T2 on T1.TableOnePK = T2.TableOneFK
WHERE T2.TableTwoValue = <target value>

And now you’ll filter TableTwo prior to the left join and then left join that result into your TableOne, meaning you’ll get a vastly different result then the first one.

Thus one need to consider such things when doing joins as if not careful, it is possible to get a vastly different join result then otherwise intended.