RE: SQL table joins: Join vs. Where -- MIDRANGE-L

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