SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Right Kind Of Join II


The Right Kind Of Join II

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

Group: General Forum Members
Points: 79268 Visits: 11400
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 (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79268 Visits: 11400
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 (293 views, 6.00 KB)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143473 Visits: 18649
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
Learn Extended Events

paul.knibbs
paul.knibbs
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: 7902 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
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9959 Visits: 12345
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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

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

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

Group: General Forum Members
Points: 6701 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 (245 views, 5.00 KB)
Paul White
Paul White
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79268 Visits: 11400
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 Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50389 Visits: 13157
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 (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79268 Visits: 11400
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