SYSK 79: Good SQL Interview Question -- What is the Left Anti Semi Join?
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
