测试一下你的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
测试请写出你的数据库服务器环境
创建测试表
--子表
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)
[php]
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行。
。。。。。。。。。。。。。。。
[/php]
可见在这里出现了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
两次的结果居然不一样,而且还都是不对的
[php]
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行。
。。。。。。。。。。。。。。。。。。。
[/php]