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

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.

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

> 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

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.

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

On Thu, 28 Jun 2007 09:33:07 +0200, "news.onet.pl"
<wkrugiolka@poczta.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

"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

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

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