MySQL中"full outer join"的实现

MySQL中"full outer join"的实现

表ta                                      表tb

  

使用：【left join】 union 【right join】

select t1.dim_a, t1.qty qty_a, t2.dim_a dim_b, t2.qty qty_b from ta t1 left join tb t2 on t1.dim_a=t2.dim_a
union 
select t1.dim_a, t1.qty qty_a, t2.dim_a dim_b, t2.qty qty_b  from ta t1 right join tb t2 on t1.dim_a=t2.dim_a

实现结果：

 合并最终的结果：

select if(t3.dim_a is null, t3.dim_b, t3.dim_a) dim_a, t3.qty_a, t3.qty_b
from (
	select t1.dim_a, t1.qty qty_a, t2.dim_a dim_b, t2.qty qty_b from ta t1 left join tb t2 on t1.dim_a=t2.dim_a
	union 
	select t1.dim_a, t1.qty qty_a, t2.dim_a dim_b, t2.qty qty_b  from ta t1 right join tb t2 on t1.dim_a=t2.dim_a
      ) t3;

 

 

源代码：

drop table if exists ta; 
create table if not EXISTS ta(	`dim_a` varchar(255),	`qty` int);
create table if not EXISTS tb(	`dim_a` varchar(255),	`qty` int);

insert into ta(dim_a,qty) values('a',343); -- 可以省略字段，初始值的时候，注意字段的顺序和类型
insert into ta values('b',324);
insert into ta values('c',563);
insert into ta values('d',7464);

insert into tb values('a',343);
insert into tb values('c',57);
insert into tb values('e',5353);
insert into tb values('f',242);

select if(t3.dim_a is null, t3.dim_b, t3.dim_a) dim_a, t3.qty_a, t3.qty_b
from (
		select t1.dim_a, t1.qty qty_a, t2.dim_a dim_b, t2.qty qty_b from ta t1 left join tb t2 on t1.dim_a=t2.dim_a
		union 
		select t1.dim_a, t1.qty qty_a, t2.dim_a dim_b, t2.qty qty_b  from ta t1 right join tb t2 on t1.dim_a=t2.dim_a
		) t3;
-- mysql中没有full join 可以使用 select if(表1.主键 is null, 表2主键, 表1主键) 主键字段 from 表 【left join】 union 【right join】 
-- Oracle中有full join