There are a lot of factors which could influence the query performance.  The
presence or absence of indexes on the relevant columns can have a profound
impact on the query plan.  Without seeing the query plans and the table
structures, it's hard to give absolute answers why one form of join might be
faster or slower than another.  Query optimizers aren't perfect, and can
sometimes choose join methods which are sub-optimum for a particular
distribution of data values.  The choice of methods (e.g.. table scan vs.
index lookup) may make sense in the general case, but may not be optimum for
your particular one.

When to use which join is a question of application requirements; the two
forms are, after all, quite different in their results, despite the
particulars of your situation.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Jerry Diegel [mailto:diegelj@g...]
Sent: Tuesday, September 10, 2002 11:18 AM
To: sql language
Subject: [sql_language] Performance consideration on table join.


I was going through some code today and I noticed one query where I had used
a LEFT OUTER JOIN versus the INNER JOIN I normally use joining those two
particular tables.  Due to the nature of the relationship between the two
tables both types of joins return the exact same set of results.  This got
me thinking...which ones faster.  With that in mind I created a handful of
queries with the different types of joins...LEFT OUTER JOIN, INNER JOIN,
LEFT JOIN, RIGHT OUTER JOIN, and a couple others.  After tracing the various
queries and viewing the reports I found that, contrary to what I would of
expected, the INNER JOIN tended to take longer and use up the most cpu.  It
had a Duration of 130 and CPU usage of 185.  I'm not exactly sure what units
those are in.  The fastest of the bunch was the LEFT JOIN with a Duration of
90 and CPU usage of 115.

My question, are there some kind of rules on when to use what join?  Any
particular reason why the INNER JOIN was so much slower and memory intensive
then the LEFT JOIN?

TIA,

Jerry