Need help understanding LEFT JOIN.

From: John Peterson <johnp_at_azstarnet.com>
Date: Sun, 28 Oct 2001 22:20:59 -0700
Message-ID: <ttppq172r4575c@corp.supernews.com>

(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 Received on Sun Oct 28 2001 - 23:20:59 CST