I come across very interesting documentation about Joins, while I was researching about article about EXCEPT yesterday. There are few interesting kind of join 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 pan.
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
Result Set:
|–Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([AdventureWorks].[Production].[Product].[ProductID], [Expr1006]) WITH UNORDERED PREFETCH)
|–Index Scan(OBJECT:([AdventureWorks].[Production].[Product].[AK_Product_rowguid]))
|–Top(TOP EXPRESSION:((1)))
|–Index Seek(OBJECT:([AdventureWorks].[Production].[WorkOrder].[IX_WorkOrder_ProductID]), SEEK:([AdventureWorks].[Production].[WorkOrder].[ProductID]=[AdventureWorks].[Production].[Product].[ProductID]) ORDERED FORWARD)
Reference : Pinal Dave (https://blog.sqlauthority.com)
