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

LEFT JOIN == LEFT OUTER JOIN, according to the SQL reference manual. No such thing as LEFT INNER JOIN.

The statement you gave is typical IBM gobbledy-gook It means that if the JOIN keyword is not qualified by one of the join operators (none of which have the word JOIN in them), then INNER is assumed. The little diagram helps, but this is not necessarily the simplest thing to wade through.

HTH

Vern

At 12:42 PM 8/16/2004, you wrote:

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