RE: next baby step in SQL: start using join

[Gord Royle <GRoyle@xxxxxxxx>]

Hi Dan

It is permissible to join in the where clause and, IMHO, it's also easier to
read. The Query optimizer will figure out the best way to do it and I think
will come to a similar conclusion as to the best path no matter which way
you do it. 

I'd have to really think to create the alternate join syntax where t1 is
joined to t2 and t3 and t2 is joined to t3.

>From Graeme Burstall's DB2 cookbook.

SELECT V1.ID ,V2.JOB ,V3.NAME ID JOB NAME
FROM STAFF_V1 
JOIN 
STAFF_V2 V2 
ON V1.ID = V2.ID
JOIN
STAFF_V1 V3
ON V2.ID = V3.ID
WHERE V3.NAME LIKE 'M%'
ORDER BY V1.NAME, V2.JOB

Your's would be even more complicated.

Gord Royle 
-----Original Message-----
From: Dan [mailto:dbcemid@xxxxxxxxx]
Sent: Thursday, March 27, 2003 4:10 PM
To: midrange-l@xxxxxxxxxxxx
Subject: next baby step in SQL: start using join


<This is the completed version of the original email.  Sorry, fat thumbs
today...>

I'm somewhat of an experienced simple user of SQL, i.e., never done any
joins.

I am now have what appears to be a perfect opportunity to use SQL join to
greatly simplify my
coding.  Knowing that one can use Retrieve Query Mgmt Query (RTVQMQRY) to
take a *QRYDFN and
generate the SQL via ALWQRYDFN(*YES), I use WRKQRY to set up the query, get
it working the way I
want it to, save the *QRYDFN, and use RTVQMQRY to generate the SQL.

So, I am surprised to see that the generated SQL doesn't have any Join
clauses (?) but, instead,
does the join via WHERE, i.e.:
 FROM      PQPROD/PESTCCP T01, 
           PQPROD/PTBLCCP T02, 
           PQPROD/PJOBHDR T03  
 WHERE     T03.CMPNUM = T02.CMPNUM 
   AND     T01.JOBNUM = T03.JOBNUM 
   AND(    T01.JOBNUM = 'TACM0170' 
   AND     T02.CSTTYP = 'C')       

The join in WRKQRY looks like this:
 Field             Test     Field        
 T03.CMPNUM        EQ       T02.CMPNUM   
 T01.CCPTYP        EQ       T02.TBLID    
 T01.CCPNUM        EQ       T02.CCPNUM   
 T01.JOBNUM        EQ       T03.JOBNUM   

So, what gives?  Is the RTVQMQRY generated SQL the best way to join?

TIA, Dan

__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
_______________________________________________
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.