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
