full outer join:can somebody tell me why I receive these results?!
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