• 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.