The Right Kind Of Join II


The Right Kind Of Join II

Author
Message
Paul White
Paul White
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60880 Visits: 11396
Comments posted to this topic are about the item The Right Kind Of Join II

The previous question in this series is here: The Right Kind Of Join



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60880 Visits: 11396
I couldn't include a graphic in the answer, so here's the query and execution plan:


DECLARE @A TABLE (a INT NOT NULL UNIQUE)
DECLARE @B TABLE (b INT NOT NULL)

SELECT COUNT_BIG(*)
FROM @A
RIGHT LOOP JOIN @B ON
[@B].b = [@A].a





Notice the plan does not touch table @A.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
QotD.png (291 views, 6.00 KB)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111072 Visits: 18623
Nice question. Better than the question is the explanation given.

Thanks Paul.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

paul.knibbs
paul.knibbs
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6362 Visits: 6240
This is one of those questions where the common sense answer (e.g. both tables have no data in, so of course the answer will be zero rows!) is actually right...I knew nothing about the stuff explained in the answer, so I learned something today.
Steve Hall
Steve Hall
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7922 Visits: 12066
I normally don't comment on QOD articles, but the explanation and screen-shot of the execution plan deserves merit.

Steve Hall
Linkedin
Blog Site
Iulian -207023
Iulian -207023
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3535 Visits: 1248
Indeed, the question and the explanation are great.

Thank you,
Iulian
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5393 Visits: 3575
Got it wrong (yes, I didn't run the code) :-D
Excellent question which shows how the query optimizer works.

A similar example is how the INNER JOIN is removed (resulting in only a scan on table "child") in the following example, due to the fact that parent_id cannot be NULL and has a FK reference to table "parent".

create table parent(id int primary key)
create table child(id int primary key, parent_id int not null references parent(id))

select c.*
from dbo.child c
inner join parent p on p.id = c.parent_id




Attachments
exec_plan.png (242 views, 5.00 KB)
Paul White
Paul White
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60880 Visits: 11396
Nils Gustav Stråbø (9/1/2011)
A similar example is how the INNER JOIN is removed (resulting in only a scan on table "child") in the following example, due to the fact that parent_id cannot be NULL and has a FK reference to table "parent".

There's an interesting limitation here: this simplification does not work if the FK relationship uses a compound key (another good reason to use surrogate keys, some would say).

https://connect.microsoft.com/SQLServer/feedback/details/683411/dri-referential-integrity-does-not-optimize-if-compound-key-is-used-for-the-key-relation



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39418 Visits: 12880
Excellent question and explanation.

But it makes the behaviour in the first right join question seem even more bizarre than it seemed before. In this case there's a query that by very simple but not blindingly obvious reasoning can be shown to be equivalent to the original query and eliminates right loop join, and despite that lack of blinding obviousness the optimiser does the transformation and provides a plan. In the first case there's a blindingly obvious equivalent query (using left join) that doesn't run into the "no loop with right join" restriction, and the optimiser doesn't do the transformation despite the total obviousness. Not only is the statement in BoL misleading, and the actual behaviour with right loop inconsistent, but also the optimiser's choice of when to produce a plan and when not seems perverse! Or actually, it's the whole concept of "no loop with right join" that is ridiculous and perverse, because every right join is (trivially) equivalent to a left join, and there's no "no loop with left join" restriction - to make any sense at all the restriction would have to be purely syntactic, with no semantic content, but this QoTD demonstrates that it is not a syntactic restriction.

Tom

Paul White
Paul White
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60880 Visits: 11396
Hi Tom,

The third question in this series will be published next week (8 September).
I promise your question will be answered then.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search