MySQL中“full outer join“的实现

一: 先创建两个表

二: 使用【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】 <br>-- Oracle中有full join