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. Here it is: what is the Left Semi Join Showplan Operator?
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
Where You Will Meet the Left Semi Join Showplan Operator
In real plans, you usually see a semi join when a query uses EXISTS, or IN with a subquery. NOT EXISTS and NOT IN usually give an anti semi join. In a graphical plan, the join itself shows as Nested Loops, Hash Match or Merge Join, and the semi join appears as its logical operation in the properties.
The key point for an interview is that a semi join returns each row from the first input at most once, even when the second input has many matches. An INNER JOIN would repeat that row once for each match. One more tip: if the subquery column can hold NULL, NOT IN and NOT EXISTS can return different results, so I prefer NOT EXISTS.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
