oracle full outer join,测试一下你的Oracle有Full Outer Join的Bug么？9i以上请进（2）

Bug2: FULL OUTER JOIN = LEFT OUTER JOIN

奇怪的是，如果将

select a.*,b.mc MC1, c.mc MC2

from ts a,tdm b, tdm c

where A.dm1 = B.DM(+) AND B.type(+)='1'

and A.dm2 = c.DM(+) AND c.type(+)='2'

创建为视图

create view vts as select a.*,b.mc MC1, c.mc MC2

from ts a,tdm b, tdm c

where A.dm1 = B.DM(+) AND B.type(+)='1'

and A.dm2 = c.DM(+) AND c.type(+)='2'

/

则下面的查询结果就是正确的

[@more@]代码:SQL>select s.*,p.*from

2 vts s full outer join

3(select*from(select tp.*,rank()over(order by id)rn from tp) --where rn<10

4)p

5 on s.pid=p.id

6/ID PID DM1 DM2 MC1 MC2 ID CON RN---- ---- ---- ---- ---------- ---------- ---- ---- ----------3 1 a ss AA SSSS 1 a 1

1 1 a aa AA AAAA 1 a 1

2 2 b BB 2 b 2

5 3 c CC 3 c 3

4 3 a ss AA SSSS 3 c 3

9 a AA

10 9

7 7 c ee CC

6 f

已选择9行。

。。。。。。。。但是加上一个无关紧要的条件限制后，查询结果就不正确了，其结果也相当于是左连接代码:SQL>select s.*,p.*from

2 vts s full outer join

3(select*from(select tp.*,rank()over(order by id)rn from tp)where rn<10

4)p

5 on s.pid=p.id

6/ID PID DM1 DM2 MC1 MC2 ID CON RN---- ---- ---- ---- ---------- ---------- ---- ---- ----------3 1 a ss AA SSSS 1 a 1

1 1 a aa AA AAAA 1 a 1

2 2 b BB 2 b 2

5 3 c CC 3 c 3

4 3 a ss AA SSSS 3 c 3

9 a AA

10 9

7 7 c ee CC

已选择8行。

。。。。。。。代码里用的rank而没用rownum，因为我这里一用rownum就出现Ora-03113

若将select * from (select tp.*,rank()over(order by id) rn from tp) where rn<10也创建为一个视图

create view vtp as select * from (select tp.*,rank()over(order by id) rn from tp) where rn<10

则Full Outer Join的查询结果是正确的代码:SQL>select s.*,p.*from

2 vts s full outer join

3 vtp p

4 on s.pid=p.id

5/ID PID DM1 DM2 MC1 MC2 ID CON RN---- ---- ---- ---- ---------- ---------- ---- ---- ----------3 1 a ss AA SSSS 1 a 1

1 1 a aa AA AAAA 1 a 1

2 2 b BB 2 b 2

5 3 c CC 3 c 3

4 3 a ss AA SSSS 3 c 3

9 a AA

10 9

7 7 c ee CC

6 f 4

已选择9行。........................