Performance between Standard Join and Inner Join

[Chamnap]

Hello, everyone

I have one question about the standard join and inner join, which one
is faster and more reliable? Can you recommend me to use? Please,
explain me...

Thanks
Chamnap

[Ed Murphy]

Re: Performance between Standard Join and Inner Join

Chamnap wrote:

I have one question about the standard join and inner join, which one
is faster and more reliable? Can you recommend me to use? Please,
explain me...

If by "standard join" you mean just JOIN, then they're
identical in effect.

[Chamnap]

Re: Performance between Standard Join and Inner Join

I mean standard join by:
Select * From Quiz, Question Where Quiz.id = Question.id

[news.onet.pl]

Re: Performance between Standard Join and Inner Join

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, Wojtas

https://www.e-krug.com

[Ed Murphy]

Re: Performance between Standard Join and Inner Join

Chamnap wrote:

I mean standard join by:
Select * From Quiz, Question Where Quiz.id = Question.id

That's arguably not a join at all, though any reasonable database
server will optimize it into one under the covers. These days,
recommended practice is to do something like

select (list of columns)
from quiz x
join question y on x.id = y.id

For one thing, when you have a less trivial case than this example,
it makes it harder to leave out a join condition by mistake.

[Dan Guzman]

Re: Performance between Standard Join and Inner Join

I have one question about the standard join and inner join, which one

is faster and more reliable? Can you recommend me to use? Please,
explain me...

The ANSI-92 style INNER JOIN syntax is is recommended in Microsoft SQL
Server. The older join syntax is still accepted and both should provide the
same level of performance and reliability.

In the case of OUTER JOINs, older style joins (*= and =*) are sometimes
ambiguous (unreliable) so the ANSI-92 style OUTER JOIN syntax is strongly
recommended. The older style outer joins are only allowed in databases
with compatibility level lower than 90 and may not be supported in future
SQL Server versions.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Chamnap" <chamnapchhorn@ gmail.comwrote in message
news:1183007085 .051745.221660@ i38g2000prf.goo glegroups.com.. .

Hello, everyone
>
I have one question about the standard join and inner join, which one
is faster and more reliable? Can you recommend me to use? Please,
explain me...
>
Thanks
Chamnap
>

[Roy Harvey]

Re: Performance between Standard Join and Inner Join

On Thu, 28 Jun 2007 09:33:07 +0200, "news.onet. pl"
<wkrugiolka@poc zta.onet.plwrot e:

>BTW left join is faster than inner join...

Really? What makes you think that? It is certainly not what I have
experienced, or heard about.

Roy Harvey
Beacon Falls, CT

[Gert-Jan Strik]

Re: Performance between Standard Join and Inner Join

"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

[bill.bertovich@gmail.com]

Re: Performance between Standard Join and Inner Join

On Jun 28, 12:38 pm, Gert-Jan Strik <s...@toomuchsp amalready.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

[Alex Kuznetsov]

Re: Performance between Standard Join and Inner Join

On Jun 28, 2:33 am, "news.onet. pl" <wkrugio...@poc zta.onet.plwrot e:

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?

[news.onet.pl]

Re: Performance between Standard Join and Inner 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

[Dan Guzman]

Re: Performance between Standard Join and Inner Join

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@poc zta.onet.plwrot e 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
>
>

[Gert-Jan Strik]

Re: Performance between Standard Join and Inner Join

"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