測試一下你的Oracle有Full Outer Join的Bug麼？9i以上請進

BUG描述：FULL OUTER JOIN <> LEFT OUTER JOIN UNION RIGHT OUTER JOIN
不知道Oracle網站上有沒有相關描述，我在網上相關資訊只搜尋到left outer join可使使用者可以查詢任何表

我的資料庫伺服器環境：OS：Windows 2000 Server DB：Oracle 9.2.0.1
測試請寫出你的資料庫伺服器環境[@more@]建立測試表
--子表
CREATE TABLE TS
(
ID VARCHAR2(4 BYTE),
PID VARCHAR2(4 BYTE),
DM1 VARCHAR2(4 BYTE),
DM2 VARCHAR2(4 BYTE)
);
--父表，ID與子表PID對應
CREATE TABLE TP
(
ID VARCHAR2(4 BYTE),
CON VARCHAR2(4 BYTE)
);
--程式碼表
CREATE TABLE TDM
(
TYPE VARCHAR2(4 BYTE),
DM VARCHAR2(4 BYTE),
MC VARCHAR2(10 BYTE)
);
插入測試資料
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'1', '1', 'a', 'aa');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'2', '2', 'b', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'3', '1', 'a', 'ss');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'4', '3', 'a', 'ss');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'5', '3', 'c', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'7', '7', 'c', 'ee');
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'9', NULL, 'a', NULL);
INSERT INTO TS ( ID, PID, DM1, DM2 ) VALUES (
'10', '9', NULL, NULL);
COMMIT;
INSERT INTO TP ( ID, CON ) VALUES (
'1', 'a');
INSERT INTO TP ( ID, CON ) VALUES (
'2', 'b');
INSERT INTO TP ( ID, CON ) VALUES (
'3', 'c');
INSERT INTO TP ( ID, CON ) VALUES (
'6', 'f');
COMMIT;
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'a', 'AA');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'b', 'BB');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'c', 'CC');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'd', 'DD');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'1', 'e', 'EE');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'aa', 'AAAA');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'ss', 'SSSS');
INSERT INTO TDM ( TYPE, DM, MC ) VALUES (
'2', 'dd', 'DDDD');
COMMIT;
表TS與表TDM外連線兩次，查出DM1和DM2對應的MC
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'
這個sql將會在下面的測試中多次用到
（其實只要3和6都做了就能看到這個BUG）
1）測試LEFT OUTER JOIN
select s.*,p.* from
(
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'
) s left outer join
(
select * from tp
) p
on s.pid=p.id
2）測試RIGHT OUTER JOIN
select s.*,p.* from
(
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'
) s right outer join
(
select * from tp
) p
on s.pid=p.id
3）測試LEFT OUTER JOIN UNION RIGHT OUTER JOIN
select s.*,p.* from
(
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'
) s left outer join
(
select * from tp
) p
on s.pid=p.id
union
select s.*,p.* from
(
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'
) s right outer join
(
select * from tp
) p
on s.pid=p.id
4）測試LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN
select s.*,p.* from
(
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'
) s left outer join
(
select * from tp
) p
on s.pid=p.id
union all
select s.*,p.* from
(
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'
) s right outer join
(
select * from tp
) p
on s.pid=p.id
5）測試內連線(INNER JOIN)
select s.* from
(
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'
) s inner join
(
select * from tp
) p
on s.pid=p.id
6）測試全外連線（FULL OUTER JOIN）
select s.*,p.* from
(
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'
) s full outer join
(
select * from tp
) p
on s.pid=p.id
下面是測試結果（只列舉了3和6）

程式碼:


SQL> select * from ts;


ID   PID  DM1  DM2
---- ---- ---- ----
1    1    a    aa

2    2    b

3    1    a    ss

4    3    a    ss

5    3    c

7    7    c    ee

9         a

10   9



已選擇8行。



SQL> select * from tp;


ID   CON
---- ----
1    a

2    b

3    c

6    f



SQL> select * from tdm;


TYPE DM   MC
---- ---- ----------
1    a    AA

1    b    BB

1    c    CC

1    d    DD

1    e    EE

2    aa   AAAA

2    ss   SSSS

2    dd   DDDD



已選擇8行。



SQL> select s.*,p.* from

  2  (

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

  4  from ts a,tdm b, tdm c

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

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

  7  ) s full outer join

  8  (

  9  select * from tp

 10  )  p

 11  on s.pid=p.id

 12  /


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

1    1    a    aa   AA         AAAA       1    a

2    2    b         BB                    2    b

5    3    c         CC                    3    c

4    3    a    ss   AA         SSSS       3    c

9         a         AA

10   9

7    7    c    ee   CC

                                          6    f

                                          2    b



已選擇10行。



SQL> select s.*,p.* from

  2  (

  3  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

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

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

  6  ) s left outer join

  7  (

  8  select * from tp

  9  )  p

 10  on s.pid=p.id

 11  union

 12  select s.*,p.* from

 13  (

 14  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

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

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

 17  ) s right outer join

 18  (

 19  select * from tp

 20  )  p

 21  on s.pid=p.id

 22  /


ID   PID  DM1  DM2  MC1        MC2        ID   CON
---- ---- ---- ---- ---------- ---------- ---- ----
1    1    a    aa   AA         AAAA       1    a

10   9

2    2    b         BB                    2    b

3    1    a    ss   AA         SSSS       1    a

4    3    a    ss   AA         SSSS       3    c

5    3    c         CC                    3    c

7    7    c    ee   CC

9         a         AA

                                          6    f



已選擇9行。

。。。。。。。

可見在這裡出現了FULL OUTER JOIN <> LEFT OUTER JOIN UNION RIGHT OUTER JOIN的現象，而按常理這兩個應該是相等的
即FULL OUTER JOIN = LEFT OUTER JOIN UNION RIGHT OUTER JOIN並且還有
LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN - INNER JOIN = FULL OUTER JOIN
如果表TS與表TDM只外連線一次，那麼不會出現這個現象。

另外還發現一個有趣的怪現象，對錶TP做一個簡單的變換（並不影響其結果集的記錄數），做一次full outer join
然後加上一個條件限制（也不影響其結果集的記錄數），再做一次full outer join
兩次的結果居然不一樣，而且還都是不對的

程式碼:


SQL> select s.*,p.* from

  2  (

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

  4  from ts a,tdm b, tdm c

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

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

  7  ) s full outer join

  8  (

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

 10  )  p

 11  on s.pid=p.id

 12  /


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

                                          2    b



已選擇10行。



這個和剛才的測試6效果一樣，最後一條記錄是多餘的



SQL> select s.*,p.* from

  2  (

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

  4  from ts a,tdm b, tdm c

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

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

  7  ) s full outer join

  8  (

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

 10  )  p

 11  on s.pid=p.id

 12  /


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



這個結果跟左連線沒啥區別-V-


SQL> select s.*,p.* from

  2  (

  3  select a.*,b.mc MC1, c.mc MC2 from ts a,tdm b, tdm c

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

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

  6  ) s left outer join

  7  (

  8  select * from tp

  9  )  p

 10  on s.pid=p.id

 11  /


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

1    1    a    aa   AA         AAAA       1    a

2    2    b         BB                    2    b

5    3    c         CC                    3    c

4    3    a    ss   AA         SSSS       3    c

9         a         AA

10   9

7    7    c    ee   CC



已選擇8行。



。。。。。。。。。。。