On Jun 28, 2:33 am, "news.onet.pl" <wkrugio...@poczta.onet.pl> wrote:
> > Select * From Quiz, Question Where Quiz.id = Question.id
>
> IMHO today inner join is a standard
>
> BTW left join is faster than inner join...
>
> Regards, Wojtaswww.e-krug.com

what about right join?

> For starters, an outer join (such as left join) will only return the
> same result as an inner join if no rows from the outer table would be
> eliminated when running the inner 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

> Yes, but when left and inner join returns same results - left is faster

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@poczta.onet.pl> wrote in message
news:f6aa7t$e7p$1@news.onet.pl...
>> For starters, an outer join (such as left join) will only return the
>> same result as an inner join if no rows from the outer table would be
>> eliminated when running the inner 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
>
>

>> However, I think using LEFT JOINs instead of INNER JOINs is not the proper solution.<<

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:
>
> > For starters, an outer join (such as left join) will only return the
> > same result as an inner join if no rows from the outer table would be
> > eliminated when running the inner 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....

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@toomuchspamalready.nl) writes:
> 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

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@sommarskog.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