有时候,我们需要得到全连接的效果,如下例:
create table t1 (id1 int, name1 varchar(20))
create table t2 (id2 int, name2 varchar(20))
insert into t1 values(1,'a')
insert into t1 values(2,'b')
insert into t2 values(1,'c')
insert into t2 values(3,'d')
create table t2 (id2 int, name2 varchar(20))
insert into t1 values(1,'a')
insert into t1 values(2,'b')
insert into t2 values(1,'c')
insert into t2 values(3,'d')
我们想得到如下结果:
1 a c
2 b
3 d
对于这种需求,理所当然地想到全连接FULL JOIN:
select
case when t1.id1 is null then t2.id2 else t1.id1 end as id,
name1,name2
from t1
full join t2
on t1.id1=t2.id2
case when t1.id1 is null then t2.id2 else t1.id1 end as id,
name1,name2
from t1
full join t2
on t1.id1=t2.id2
但是全连接的效率较差,可以改为:
select id1,max(name1) name1,max(name2) name2
from
(
select id1,name1,'' as name2 from t1
union all
select id2,'' as name1,name2 from t2
) t
group by id1
from
(
select id1,name1,'' as name2 from t1
union all
select id2,'' as name1,name2 from t2
) t
group by id1