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行。

。。。。。。。

代码:


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行。
........................