如何理解full join
发布时间 2023-07-18 11:06:05作者: 鸠兹
select 1 as id , '张三' as name from dual union all
select 2 as id , '李四' as name from dual union all
select 2 as id , '李四' as name from dual union all
select 3 as id , '王五' as name from dual
) A full join
(
select 1 as id , '张三' as name from dual union all
select 2 as id , '李四' as name from dual union all
select 5 as id , '李六' as name from dual
) B on A.id = B.id
with a as (
select 1 as id , '张三' as name from dual union all
select 2 as id , '李四' as name from dual union all
select 2 as id , '李四' as name from dual union all
select 3 as id , '王五' as name from dual
), b as (
select 1 as id , '张三' as name from dual union all
select 2 as id , '李四' as name from dual union all
select 5 as id , '李六' as name from dual
)
select a.id ,a.name ,b.id as bid ,b.name as bname from a left join b on a.id =b.id
union all
select a.id ,a.name ,b.id as bid ,b.name as bname from a right join b on a.id =b.id where a.id is null
总结 full join 相当于 A表和B左关联的基础上 ,再合并A表和B右关联,并且在此基础上加上A表的关联字段为NULL