The left anti semi join is a logical operator (read: you’ll see it referred to in the execution plan, but there is no explicit T-SQL equivalent) which returns each row from the first (top) input when there is no matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row.

 

Using NOT IN operator, e.g.

    SELECT * FROM T1

    WHERE T1.foreign_key

    NOT IN (SELECT key FROM T2)

is one example resulting in the left anti semi join logical operator.

 

Another example of achieving this kind of join is:

    SELECT * FROM T1 LEFT JOIN T2

    ON T1.foreign_key = T2.key

    WHERE T2.key IS NULL

 

Sources:  http://msdn2.microsoft.com/en-us/library/ms191171.aspx

http://www.mcse.ms/archive94-2004-5-712832.html