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


 


 

Comments (0)

Skip to main content