之前在uself的"在oracle中使用left join达不到预期结果"的帖子里发了关于join的认识,后来发现其实full join是可以完成那个查询的。先做下边的例子的纠错eg:关于join的(server2005里的 oracle里结果一样)... create table testfulljoin
(a char(4),
b char(4),
c char(4))
insert testfulljoin(a,b,c)
select 'a','b','c' union all
select 'b','a','d' union all
select 'c','d','e' union all
select 'd','f','g'
create table testfulljoin2
(b char(4),
c char(4),
d char(4))
insert testfulljoin2(b,c,d)
select 'b','c','d' union all
select 'd','e','g' union all
select 'f','d','g' union all
select 'd','e','c' 1)right join
select a.* ,b.d from testfulljoin2 as b right join testfulljoin as a on a.b=b.b and a.c=b.c;
a b c d
b a d NULL
c d e g
c d e c
d f g NULL
2)left join
select a.* ,b.d from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;
a b c d
c d e g
NULL NULL NULL g
c d e c -- 上面的left join的查询结果可能和你要查询的结果不同,实际上它查询的结果是正确的。如果你改写上面的Sql语句如下面的:
select a.a ,b.* from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;
或者
select a.a,isnull(a.b,b.b) as b,isnull(a.c,b.c)as c,b.d from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;
--那么结果则会如下:
a b c d
c d e g
NULL f d g
c d e c 3)full join
select a.* ,b.d from testfulljoin2 as b full join testfulljoin as a on a.b=b.b and a.c=b.c;
a b c d
c d e g
NULL NULL NULL g
c d e c
b a d NULL
d f g NULL --同理修改上面的full join代码
select a.a,isnull(a.b,b.b),isnull(a.c,b.c),b.d from testfulljoin2 as b full join testfulljoin as a on a.b=b.b and a.c=b.c;
结果如下:
a b c d
c d e g
NULL f d g
c d e c
b a d NULL
d f g NULL4)inner join
select a.* ,b.d from testfulljoin2 as b inner join testfulljoin as a on a.b=b.b and a.c=b.c;
a b c d
c d e g
c d e c 所以uself的问题可以写如下的full join来完成(Oracle Syntax)
select nvl(a.bjmc,b.bjmc) bjmc,nvl(a.kcmc,b.kcmc) kcmc ,nvl(a.xf,b.xf) xf,b.zf from kcb a
full join cjb b
on a.bjmc=b.bjmc
and a.kcmc=b.kcmc
and b.bjmc='中文0601'
and b.name='位东方';
结果:
BJMC KCMC XF ZF
------------ ---------- ---------- ----------
中文0601 古代文学 3 68
中文0601 现代文学 3 82
中文0601 修辞 4 79
中文0601 写作 4 84
中文0601 文学概论 2
中文0601 现代汉语 3 90相信有了上面的例子,我们对join有个不再模糊的认识~~ 希望大家再完善 评论~~
(a char(4),
b char(4),
c char(4))
insert testfulljoin(a,b,c)
select 'a','b','c' union all
select 'b','a','d' union all
select 'c','d','e' union all
select 'd','f','g'
create table testfulljoin2
(b char(4),
c char(4),
d char(4))
insert testfulljoin2(b,c,d)
select 'b','c','d' union all
select 'd','e','g' union all
select 'f','d','g' union all
select 'd','e','c' 1)right join
select a.* ,b.d from testfulljoin2 as b right join testfulljoin as a on a.b=b.b and a.c=b.c;
a b c d
b a d NULL
c d e g
c d e c
d f g NULL
2)left join
select a.* ,b.d from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;
a b c d
c d e g
NULL NULL NULL g
c d e c -- 上面的left join的查询结果可能和你要查询的结果不同,实际上它查询的结果是正确的。如果你改写上面的Sql语句如下面的:
select a.a ,b.* from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;
或者
select a.a,isnull(a.b,b.b) as b,isnull(a.c,b.c)as c,b.d from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;
--那么结果则会如下:
a b c d
c d e g
NULL f d g
c d e c 3)full join
select a.* ,b.d from testfulljoin2 as b full join testfulljoin as a on a.b=b.b and a.c=b.c;
a b c d
c d e g
NULL NULL NULL g
c d e c
b a d NULL
d f g NULL --同理修改上面的full join代码
select a.a,isnull(a.b,b.b),isnull(a.c,b.c),b.d from testfulljoin2 as b full join testfulljoin as a on a.b=b.b and a.c=b.c;
结果如下:
a b c d
c d e g
NULL f d g
c d e c
b a d NULL
d f g NULL4)inner join
select a.* ,b.d from testfulljoin2 as b inner join testfulljoin as a on a.b=b.b and a.c=b.c;
a b c d
c d e g
c d e c 所以uself的问题可以写如下的full join来完成(Oracle Syntax)
select nvl(a.bjmc,b.bjmc) bjmc,nvl(a.kcmc,b.kcmc) kcmc ,nvl(a.xf,b.xf) xf,b.zf from kcb a
full join cjb b
on a.bjmc=b.bjmc
and a.kcmc=b.kcmc
and b.bjmc='中文0601'
and b.name='位东方';
结果:
BJMC KCMC XF ZF
------------ ---------- ---------- ----------
中文0601 古代文学 3 68
中文0601 现代文学 3 82
中文0601 修辞 4 79
中文0601 写作 4 84
中文0601 文学概论 2
中文0601 现代汉语 3 90相信有了上面的例子,我们对join有个不再模糊的认识~~ 希望大家再完善 评论~~
create table testfulljoin
(a char(4),
b char(4),
c char(4))
insert testfulljoin(a,b,c)
select 'a','b','c' union all
select 'b','a','d' union all
select 'c','d','e' union all
select 'd','f','g'