It is very common of interviewers to ask questions which are a bit off and sometimes not used in daily life. Here is such question I heard the other dya.
Question: What is Left Semi Join Showplan Operator?
Answer:
There are few interesting kinds of joint operations exists when execution plan is displayed in text format.
Left Semi Join Showplan Operator
The Left Semi Join operator returns each row from the first (top) input when there is a matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row.
Left Anti Semi Join Showplan Operator
The Left Anti Semi Join operator 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.
Right Anti Semi Join Showplan Operator
The Right Anti Semi Join operator outputs each row from the second (bottom) input when a matching row in the first (top) input does not exist. A matching row is defined as a row that satisfies the predicate in the Argument column (if no predicate exists, each row is a matching row).
Right Semi Join Showplan Operator
The Right Semi Join operator returns each row from the second (bottom) input when there is a matching row in the first (top) input. If no join predicate exists in the Argument column, each row is a matching row.
Following script will display Left Anti Semi Join Showplan Operator in the result pane.
USE AdventureWorks;
GO
SET SHOWPLAN_TEXT ON
GO
SELECT ProductID
FROM Production.Product
WHERE ProductID
NOT IN (
SELECT ProductID
FROM Production.WorkOrder);
GO
SET SHOWPLAN_TEXT OFF
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
