RE: SQL table joins: Join vs. Where

["Himes, Jay" <jehimes@xxxxxxxxxxx>]

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

> 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

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