RE: SQL table joins: Join vs. Where

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: CWilt@xxxxxxxxxxxx
> 
> I'd like to know where you got that information, because as I
understand
> it
> that is simply not the case.  Both queries would be run exactly the
same
> way.

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                  


Joe
http://forums.plutabrothers.com/IAAI