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

Responses inline

At 05:31 PM 8/11/2004, you wrote:

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx / Vern Hamberg
> Sent: Wednesday, August 11, 2004 6:12 PM
>
> The information on QRYDFN conversion is in chapter 12 of the V5R2 Query
> Management Programming manual. A search on QRYDFN might be more
> fruitful, too.

Oh, I thought you were pointing me to a discussion of a T01 <-- T02 <-- T03
multiple joins.  OK, I see the section on conversion.  It's apparent that
the conversion is pretty basic, eh?

Yeah, you must have had a type 1 join in Query. My first answer was more directed at the situation where the type 2 or 3 options are converted as if they were type 1 - that's the part that is not supported correctly with RTVQMQRY. ANZQRY reports this, BTW.

> You have "AMFLIB/CIVSPCL0 T02" specified twice. The second one follows a
> comma, which is also invalid.
>
> JOINs are cumulative. Each JOIN works against the result of the previous
> JOIN. So the columns you want for the JOIN to T03 are there from the
> previous set of JOINs. Try this:

YESSSSSSS!  That was it!  Although, I admit to being confused by the syntax.
How does the SQL processor know to join T03 to T02 and not to the others?
Is it the field matching referring to T03 & T02 fields in the "on ("
section?  Or does this get more into the black box magic of SQL and most of
the time I won't care how it does it, it just does?

Cool!! Conceptually, each JOIN creates a temporary result set, which becomes the left side of the next JOIN spec., etc. IIRC, there is no column selection at this point, so all columns exist in the temp result. In your case, T02 is joined to T01. Then T04 is joined to the result of joining T02 to T01. Finally, T03 is joined to that last result, which by now has the fields from T02 in its "record" definition. The "on (" part defines the relationships. This is a case where SQL & Query/400 both have an advantage over join logicals, because the joins are not limited to linking to the primary.

The optimizer might not do things in the listed order, if it decides that it can run the query faster some other way. This is the black-box stuff. Sometimes you have to help it, esp. the classic engine. It does not handle so-called transitive join criteria very well - things where there is a field that joins all 3 files. So

select * from a join b
              on b.join1 = a.join1
            join c
              on c.join1 = b.join1

might not perform as well as

select * from a join b
              on b.join1 = a.join1
            join c
              on c.join1 = b.join1 and
                   c.join1 = a.join1

The extra information is not derived from the first form and might help to select better indexes. I believe the new engine is trying to be better at this - hope so, anyway!

Vern

Thanks Vern!

db

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.