RE: SQL table joins: Join vs. Where

[CWilt@xxxxxxxxxxxx]

No,

The both perform inner joins.

>From the manual:
"If a join-operator is not specified, INNER is implicit."

To get an outer join you must explicitly specify OUTER.

HTH,
Charles


> -----Original Message-----
> From: Himes, Jay [mailto:jehimes@xxxxxxxxxxx]
> Sent: Monday, August 16, 2004 1:13 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL table joins: Join vs. Where
> 
> 
> It looks to me as if the first query will do an inner join; while the
> second does an outer join. To get an outer join using a where clause I
> think you need to allow for a null in the outer table.
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
> Sent: Monday, August 16, 2004 11:02 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL table joins: Join vs. Where
> 
> 
> Charles, as far as I can tell, you are exactly correct.  
> After I fixed a
> typo in the test suite, my benchmarks show FETCH via WHERE 
> and FETCH via
> JOIN performing exactly the same (within 0.1% of each other).
> 
> My WHERE vs. JOIN test:
> 
> c/EXEC SQL select a.key1, b.key1, c.key1, c.data1    
> c+           into :akey1, :bkey1, :ckey1, :cdata1    
> c+           from IBPP1 as a, IBPP2 as b, IBPT1 as c 
> c+           where a.key1 = :keya                    
> c+             and a.fk1 = b.key1 and a.fk2 = c.key1
> c/END-EXEC                                           
> 
> runs nearly identically to:
> 
> c/EXEC SQL select a.key1, b.key1, c.key1, c.data1  
> c+           into :akey1, :bkey1, :ckey1, :cdata1  
> c+           from IBPP1 as a                       
> c+           left join IBPP2 as b on a.fk1 = b.key1
> c+           left join IBPT1 as c on a.fk2 = c.key1
> c+           where a.key1 = :keya                  
> 
>