Performance between Standard Join and Inner Join

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

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.

Re: Performance between Standard Join and Inner Join

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

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
www.e-krug.com

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.

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" wrote in message
news:1183007085.051745.221660@i38g2000prf.googlegroups.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
>

Re: Performance between Standard Join and Inner Join

On Thu, 28 Jun 2007 09:33:07 +0200, "news.onet.pl"
wrote:

>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

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

Re: Performance between Standard Join and Inner Join

On Jun 28, 12:38 pm, Gert-Jan Strik
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

Re: Performance between Standard Join and Inner Join

>> the standard join and inner join, which one is faster and more reliable? <<

The infixed INNER JOIN and the WHERE clause join produce the same
results. In SQL Server, they also produce the same execution plan and
I thought that the choice was a matter of style. It turns out that is
not true in DB2. A friend asked for help optimizing a very large
query for a report and found out that the older syntax did better.

I am still trying to figure out why this happened. My best guess is
that the infixed operators are required to behave as if they are
executed in left to right order. This is vital or OUTER JOINs, but
should not make a difference for INNER JOINs. But does their
optimizer (which is pretty darn good) get fooled into an acceptable
result with infixed notation, but do more investigation with a FROM
simple clause? I am still waiting to hear back from DB2 experts.

Re: Performance between Standard Join and Inner Join

On Jun 28, 2:33 am, "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...
>
> Regards, Wojtaswww.e-krug.com

what about right join?

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

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" 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
>
>

Re: Performance between Standard Join and Inner Join

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

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

Re: Performance between Standard Join and Inner Join

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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx