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)
4 Comments. Leave new
I cannot figure out how to do the following, and it is really irking me:
I have, say, 6 tables, I want to include all of the records associated with the first table’s ID value, somewhere down the line in the joins, however, I want to get any values that exist between two child tables and still return results if neither of them exist.
Like:
select * from house left join person on house.houseid=person.houseid –Now here is the tricky part
some join cat on person.catID=cat.catID
I want to return all houses. I want to return all persons. Left and full joins will not return the records if neither a person or a cat exists…
What can I do with this?
Thanks for your help.
Frank
I know this is a year ago, but for anyone who’s having this issue like Frank, you can easily use a full outer join to accomplish this goal.
Assume that the column a is the cat_id.
declare @cat table(a int, b int)
declare @person table(a int, b int)
insert into @cat
select 1,1
union
select 2,2
insert into @person
select 1,1
union
select 3,3
select *
from @cat m
full outer join @person m2
on m.a = m2.a
I am not clear with any of these. Please put some light on it what exactly it do.
“If no join predicate exists in the Argument column, each row is a matching row.”
If a Left Anti Semi join is taking 75% of the query cost is there any way to mitigate that cost.
You’re explanation seems to just be the MSDN info represented with an example!