SQL SERVER – 2005 Explanation Left Semi Join Showplan Operator and Other Operator

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 (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – 2005 Explanation Left Semi Join Showplan Operator and Other Operator

  1. 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

  2. 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

  3. 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.”

  4. 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!

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s