Posted by bill.bertovich on 06/28/07 22:42

On Jun 28, 12:38 pm, Gert-Jan Strik <s...@toomuchspamalready.nl>
wrote:
> "news.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...
>
> No it isn't.
>
> 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.
>
> Next, using inner join gives the optimizer more options how to process
> the query which on average should lead to better performance then using
> the outer join equivalent.
>
> As usual, course there is a disclaimer here too. There could be
> situations where the optimizer might select a suboptimal plan, or when
> the optimizer shortcuts its optimization process because of the many
> possible access paths. In those situations a rule based approach, or an
> outer join approach might accidentally run faster.
>
> Gert-Jan

Cardinality, distribution, indexing strategy and the optimizer
strategy are the determinants. Every posting reply is correct given
the appropriate data sets. The bottom line: prototype and test in your
environment with your data sets and system loads if you want a
definitive answer.

-- Bill

[Back to original message]