The Right Kind Of Join II
-
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[/url]
Learn Extended Events -
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.
-
Indeed, the question and the explanation are great.
Thank you,
Iulian
-
Got it wrong (yes, I didn't run the code) 😀
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
-
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).
-
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
-
Hi Tom,
The third question in this series will be published next week (8 September).
I promise your question will be answered then.
Paul
-
thanks Paul!!!
Really, this question is very interesting!!
today , I learned anything!!!!
[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life! -
Good question, I learned something new today.
http://brittcluff.blogspot.com/
-
Very interesting question, thanks Paul.
-
paul.knibbs (9/1/2011)
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.Me too. Good question and answer, thanks.
-
Thanks Kiwi! Look forward to the next installment. 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply