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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36124 Visits: 11361
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36124 Visits: 11361
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 (282 views, 6.00 KB)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67567 Visits: 18570
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
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: 4270 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 (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5207 Visits: 11878
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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

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

Thank you,
Iulian
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3693 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 (239 views, 5.00 KB)
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36124 Visits: 11361
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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26186 Visits: 12503
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36124 Visits: 11361
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