full outer join:can somebody tell me why I receive these results?!

From: <uneboite_at_hotmail.com>
Date: Thu, 21 Feb 2008 08:09:44 -0800 (PST)
Message-ID: <db3b12d9-e950-4e08-80c8-4a27f3263936@62g2000hsn.googlegroups.com>


Why do I get these results when I execute the script following this text?

In strategy A results I dont understand why T2 rows are repeated with n2 being null on the repeated rows...

In strategy B results I dont understand where T2 'e' row is? Why can't I get it in the results?
Why T2 'd' row is doubled with a 3 and 4 n2 value?

I execute this on a 9i installation

thanks in advance!!!

strategy A

N1                   COL1 N2                 COL2
---------------------- ----   ---------------------- ----
1                      a    1                      a
2                      b    2                      b
3                      c    3                      d
                                                     d
                                                     a
                                                     b
                                                     e
strategy B
N1                   COL1 N2               COL2
---------------------- ---- ---------------------- ----
1                      a    1                      a
2                      b    2                      b
3                      c    3                      d
                             4                      d


begin

   execute immediate 'drop table t1';
end;
/

begin

   execute immediate 'drop table t2';
end;
/

create table t1(col1 char(1));
create table t2(col2 char(1));

insert into t1 values ('a');
insert into t1 values ('c');
insert into t1 values ('b');

insert into t2 values ('e');
insert into t2 values ('b');

insert into t2 values ('a');
insert into t2 values ('d');

select * from t1;
select * from t2;

/* strategy A */
select *
from (select row_number() over(order by col1) as n1

           , col1
      from t1

)
full outer join (select row_number() over(order by col2) as n2 , col2 from t2
)
on n1=n2

;

/* strategy B */
select *
from (select rownum as n1

           , col1
      from (select *
            from t1
            order by col1
           )

)
full outer join (select rownum as n2 , col2 from (select * from t2 order by col2 )
)
on n2=n1

; Received on Thu Feb 21 2008 - 10:09:44 CST

Original text of this message