I did some checking on the three proposed types of outerjoins with no data in a second table:
Note: If you want data from the second table when it exists, then you should use Q3
Q1: select * from aat1
where aat1.f1 not in (select aat2.f1 from aat2)
--Exception joins should better be implemented like this:
Q2: select * from aat1
where not exists (select 1 from aat2 where aat1.f1 = aat2.f1)
Q3: SELECT a.*
FROM aat1 a
LEFT JOIN aat2 b ON a.f1=b.f1
WHERE b.f1 IS NULL
Using DBCC FREEPROCCACHE between them, IN SQL Server 2000, Q1 and Q2 have identical execution plans and estimated costs based on the small tables provided: Cost 0.0417 using a Left Anti Semi Join. Note: I did put pks on the tables and the results were equivalent but the cost slightly higher without the pks (no surprise)
Q3 came in at the same cost of: 0.0417, but it used a Nested Loops Left outer Join.
These are small tables so I tried similar queries on two parent child tables with the parent table havin a two column pk, and the child table having a 3 column pk. Row counts: Parent table: 1707989 Child Table: 431251
Once again Q1 and Q2 had the same estimated cost and used the same execution plan with a join type of Merge Join/Left Anti-Semi Join Cost: 32.8 and Q3 came in cost of 33.7 using a Merge Join/Left Outer Join.
I have had a Microsoft consultant tell me not to use the NOT IN syntax, but honestly I haven't seen proof in the execution plans.
Oh well this is some food for thought.