On Jun 28, 2:33 am, "news.onet.pl" <wkrugio... (AT) poczta (DOT) onet.pl> wrote:
what about right join?

Yes, but when left and inner join returns same results - left is faster
(probably because it doesn't check the dependencies - just join results)
We use left join instead inner in situations we know that inner join will
not cut results....


Regards, Wojtas

Can you post an example (DDL and sample data) that illustrates this
behavior, including with the execution plans?

I can see how this might happen in cases where the inner join uses an
execution plan that turns out to be sub-optimal. This could occur because
statistics are stale or the disk subsystem is unusually biased in favor of
scans (often used on outer joins). However, I think using LEFT JOINs
instead of INNER JOINs is not the proper solution.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"news.onet.pl" <wkrugiolka (AT) poczta (DOT) onet.pl> wrote

The OUTER JOINS have to be executed in left to right order, so that is
probably what the optimizer will do first. I think that detecting
when an OUTER JOIN can be replaced by an INNER JOIN so that the query
can be re-ordered would be very hard and probably impossible in the
general case.

"news.onet.pl" wrote:
I understand your case, but still I disagree. I still claim that it is
not faster, not as a rule. As a rule, it is equally fast or slower.
However, I am aware that this is mostly an academic discussion. In most
situations I would expect the same performance.

If you specify Left Join instead of Inner Join, you are basically doing
two things:
1. you are reducing the number of potential access paths during
compilation
2. you are 'forcing' the access path between the two tables: from the
outer table to the inner table

The result of [1] is positive, because a full compile would require less
time and resources. Of course this is only relevant when the optimizer
actually performs a full compile.

The result of [2] is negative, because it disqualifies query plans that
might be more efficient than the 'forced' left to right access path.
Obviously, this is only relevant if there actually is a more efficient
query plan.

The smarter the optimizer gets, the smaller the performance advantage of
Inner Join will be ([2]), and the smaller the potentially added
compilation cost will be ([1]).

The bottom line is, that you could see suboptimal performance in such a
Left Join scenario when you know that no rows from the outer table will
be eliminated but the optimizer does not.

I think this Left Join trick is a very good query hint if the query
underperforms because of a bad query plan. But using query hints without
a reason (simply out of routine) still sounds inappropriate to me.

Of course, if you have an example where a Left Join performs better than
the Inner Join equivalent, then I would be most interested to see it! If
you could post or describe such an example, that would be great.

Thanks,
Gert-Jan

Gert-Jan Strik (sorry (AT) toomuchspamalready (DOT) nl) writes:
Maybe. If the condition is over trusted non-nullable FK, the optimizer
should look through the trick. For instance:

select OD.*
from Northwind..[Order Details] OD
left join Northwind..Orders O ON OD.OrderID = O.OrderID
WHERE O.CustomerID = 'VINET'

Starts with accessing the Orders table.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx