Oracle-記一下使用full join的坑

full join就是一個坑（还是我太菜）

1. 坑1

withtmp1
as(select '1002' id, '錢二' name, 18 age  from dual
),tmp2
as(select '1001' id, '趙一' name, '山東' address from dual
),tmp3
as(select '1001' id, '趙一' name, '唱歌' hobby from dual
)
select case when a.id is not null then a.idwhen b.id is not null then b.idwhen c.id is not null then c.idelse null end id, case when a.name is not null then a.namewhen b.name is not null then b.namewhen c.name is not null then c.nameelse null end name, a.age, b.address, c.hobbyfrom tmp1 afull join tmp2 b on b.id = a.idfull join tmp3 c on c.id = a.idorder by id

期望是id=1001的数据合并，但是并没有，结果如下：

2. 坑2

若是將第二個full join的on改為c.id = b.id

withtmp1
as(select '1002' id, '錢二' name, 18 age  from dual
),tmp2
as(select '1001' id, '趙一' name, '山東' address from dual
),tmp3
as(select '1001' id, '趙一' name, '唱歌' hobby from dual
)
select case when a.id is not null then a.idwhen b.id is not null then b.idwhen c.id is not null then c.idelse null end id, case when a.name is not null then a.namewhen b.name is not null then b.namewhen c.name is not null then c.nameelse null end name, a.age, b.address, c.hobbyfrom tmp1 afull join tmp2 b on b.id = a.idfull join tmp3 c on c.id = b.idorder by id

看似出來了，實際上並不完整
2021-07-20补充：因为表a和表c没有关联

3. 也是坑

withtmp1
as(select '1002' id, '錢二' name, 18 age  from dual
),tmp2
as(select '1001' id, '趙一' name, '山東' address from dual
),tmp3
as(select '1001' id, '趙一' name, '唱歌' hobby from dual
)
select case when a.id is not null then a.idwhen b.id is not null then b.idwhen c.id is not null then c.idelse null end id, case when a.name is not null then a.namewhen b.name is not null then b.namewhen c.name is not null then c.nameelse null end name, a.age, b.address, c.hobbyfrom tmp1 afull join tmp2 b on b.id = a.idfull join tmp3 c on c.id = b.id or c.id = a.idorder by id

這樣寫的話，數據量大時速度会很慢。

4. 麻煩的寫法

withtmp1
as(select '1002' id, '錢二' name, 18 age  from dual
),tmp2
as(select '1001' id, '趙一' name, '山東' address from dual
),tmp3
as(select '1001' id, '趙一' name, '唱歌' hobby from dual
)
select nvl(a1.id,b1.id) id, nvl(a1.name,b1.name) name, a1.age, a1.address, b1.hobbyfrom(select nvl(a.id,b.id) id, nvl(a.name,b.name) name, a.age, b.addressfrom tmp1 afull join tmp2 b on b.id = a.id) a1full join tmp3 b1 on b1.id = a1.idorder by id

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！