Re: Need help understanding LEFT JOIN.

From: Stephan Langer <slanger_at_dixi-wc.de>
Date: Mon, 29 Oct 2001 10:26:15 +0100
Message-ID: <3BDD20B7.991A19BF@dixi-wc.de>

Hallo,

you have to "left join" the constants too, like

...

 where t.Field = 'Primary'
   and t.ElementId = tColA.ElementId(+)
   and t.ElementId = tColB.ElementId(+)
   and t.ElementId = tColC.ElementId(+)
->   and tColA.Field(+) = 'ColA'

-> and tColB.Field(+) = 'ColB'
-> and tColC.Field(+) = 'ColC'
...

hth
Stephan

John Peterson schrieb:

> (Oracle 8.1.7 on Win2K)
>
> Hello, all!
>
> I'm having a little trouble understanding how a LEFT JOIN works. Consider
> the following:
>
> create table Test(ElementId number not NULL,
> Ordinal number not NULL,
> Field varchar2(255),
> Data varchar2(4000));
>
> insert into Test values (1, 0, 'Primary', NULL);
> insert into Test values (2, 0, 'Primary', NULL);
> insert into Test values (3, 0, 'Primary', NULL);
> insert into Test values (4, 0, 'Primary', NULL);
> insert into Test values (5, 0, 'Primary', NULL);
>
> insert into Test values (1, 1, 'ColA', 'ColA Data for 1');
> insert into Test values (1, 2, 'ColB', 'ColB Data for 1');
>
> insert into Test values (2, 1, 'ColC', 'ColC Data for 2');
>
> insert into Test values (3, 1, 'ColC', 'ColC Data for 3');
> insert into Test values (3, 2, 'ColA', 'ColA Data for 3');
> insert into Test values (3, 3, 'ColB', 'ColB Data for 3');
>
> insert into Test values (4, 1, 'ColB', 'ColB Data for 4');
>
> select t.ElementId,
> tColA.Data as DataColA,
> tColB.Data as DataColB,
> tColC.Data as DataColC
> from Test t,
> Test tColA,
> Test tColB,
> Test tColC
> where t.Field = 'Primary'
> and t.ElementId = tColA.ElementId(+)
> and t.ElementId = tColB.ElementId(+)
> and t.ElementId = tColC.ElementId(+)
> and tColA.Field = 'ColA'
> and tColB.Field = 'ColB'
> and tColC.Field = 'ColC'
>
> I would expect (or, more precisely, want ;-) the output to be:
>
> ElementId DataColA DataColB DataColC
> --------- --------------- --------------- ---------------
> 1 ColA Data for 1 ColB Data for 1 (null)
> 2 (null) (null) ColC Data for 2
> 3 ColA Data for 3 ColB Data for 3 ColC Data for 3
> 4 (null) ColB Data for 4 (null)
> 5 (null) (null) (null)
>
> However, much to my surprise, the output is:
>
> ElementId DataColA DataColB DataColC
> --------- --------------- --------------- ---------------
> 3 ColA Data for 3 ColB Data for 3 ColC Data for 3
>
> For some reason, I appear to *only* be getting those rows that represent the
> least common denominator (typically non-NULL). Just like a "regular" JOIN
> operation
>
> Clearly my understanding of how the LEFT JOIN works is a bit "off". What am
> I doing wrong?
>
> If I were to reconstruct my query to look like:
>
> select q01.ElementId,
> q02.Data as DataColA,
> q03.Data as DataColB,
> q04.Data as DataColC
> from
> (
> select ElementId
> from Test
> where Field = 'Primary'
> ) q01,
> (
> select ElementId,
> Data
> from Test
> where Field = 'ColA'
> ) q02,
> (
> select ElementId,
> Data
> from Test
> where Field = 'ColB'
> ) q03,
> (
> select ElementId,
> Data
> from Test
> where Field = 'ColC'
> ) q04
> Where q01.ElementId = q02.ElementId(+)
> and q01.ElementId = q03.ElementId(+)
> and q01.ElementId = q04.ElementId(+)
>
> This works as I expected! But, I would have thought the original query
> would have yielded in the same results.
>
> Is there a way to make the first technique work as desired? The second
> technique is a pretty poor performer when there's a lot of rows in the Test
> table. In fact, a VERY poor performer.
>
> I would welcome any thoughts/comments/suggestions! Thanks! :-)
>
> John Peterson

--
Mit freundlichen Gr$B|_(Ben

Stephan Langer,
QITS GmbH

Received on Mon Oct 29 2001 - 03:26:15 CST