By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

Performance between Standard Join and Inner Join

P: n/a
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

Jun 28 '07 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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.
Jun 28 '07 #2

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

Jun 28 '07 #3

P: n/a
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
www.e-krug.com
Jun 28 '07 #4

P: n/a
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.
Jun 28 '07 #5

P: n/a
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" <ch***********@gmail.comwrote in message
news:11**********************@i38g2000prf.googlegr oups.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
Jun 28 '07 #6

P: n/a
On Thu, 28 Jun 2007 09:33:07 +0200, "news.onet.pl"
<wk********@poczta.onet.plwrote:
>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
Jun 28 '07 #7

P: n/a
"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
Jun 28 '07 #8

P: n/a
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

Jun 28 '07 #9

P: n/a
On Jun 28, 2:33 am, "news.onet.pl" <wkrugio...@poczta.onet.plwrote:
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?

Jul 1 '07 #10

P: n/a
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
Jul 2 '07 #11

P: n/a
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" <wk********@poczta.onet.plwrote in message
news:f6**********@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

Jul 2 '07 #12

P: n/a
"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
Jul 2 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.