RE: SQL table joins: Join vs. Where

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

According to the SQL Reference, there has been a change in the way the 
optimizer handles the 2 syntaxes. It has to do with the way join order is 
processed. I might be wrong here, but it seems that the new default 
behavior lets the optimizer do whatever it thinks will perform best, where 
the old behavior gave you control by using a different syntax. That old way 
is not what I call elegant, more like esoteric, Gnostic information known 
only by the Trilateral Commission.

A setting in the QAQQINI file, FORCE_JOIN_ORDER, has the following values:

*DEFAULT-->The default is set to *NO.

*NO-->Allow the optimizer to re-order join tables.

*SQL-->Only force the join order for those queries that use the SQL JOIN 
syntax. This mimics the behavior for the optimizer prior to V4R4M0.

*PRIMARY nnn-->Only force the join position for the file listed by the 
numeric value nnn (nnn is optional and will default to 1) into the primary 
position (or dial) for the join. The optimizer will then determine the join 
order for all of the remaining files based upon cost.

*YES-->Do not allow the query optimizer to re-order join tables as part of 
its optimization process. The join will occur in the order in which the 
tables were specified in the query.

(From Chapter 4. Optimizing query performance using query optimization tools)

Only an INNER JOIN has a direct WHERE CLAUSE equivalent. A LEFT or RIGHT 
OUTER JOIN can be simulated with UNION SELECT and NOT EXISTS constructs.

My guess is that there is little difference now between the 2 ways of 
specifying inner joins, unless you control join order with a QAQQINI setting.

Vern

At 08:34 AM 8/12/2004, you wrote:
> Dan,
>
> In SQL the statements:
>
> select A.A1, B.B1
> from A inner join B
>    on A.K1 = B.K1
>
> and
>
> select A.A1, B.B1
> from A, B
> where A.K1 = B.K1
>
> are exactly the same.  The second one is simply a shorthand form of the
> inner join.
>
> HTH,
> Charles
>
>
>
> > -----Original Message-----
> > From: Dan Bale [mailto:dbale@xxxxxxxxxxxxx]
> > Sent: Wednesday, August 11, 2004 3:49 PM
> > To: midrange-l@xxxxxxxxxxxx
> > Subject: SQL table joins: Join vs. Where
> >
> > Why doesn't RTVQMQRY convert the WRKQRY joins to SQL joins?
> > Is there a
> > performance issue between one or the other?
> >