Oracle-記一下使用full join的坑

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

1. 坑1

with
    tmp1
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.id
            when b.id is not null then b.id
            when c.id is not null then c.id
            else null end id, 
       case when a.name is not null then a.name
            when b.name is not null then b.name
            when c.name is not null then c.name
            else null end name, 
       a.age, 
       b.address, 
       c.hobby
    from tmp1 a
        full join tmp2 b on b.id = a.id
        full join tmp3 c on c.id = a.id
    order by id

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

2. 坑2

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

with
    tmp1
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.id
            when b.id is not null then b.id
            when c.id is not null then c.id
            else null end id, 
       case when a.name is not null then a.name
            when b.name is not null then b.name
            when c.name is not null then c.name
            else null end name, 
       a.age, 
       b.address, 
       c.hobby
    from tmp1 a
        full join tmp2 b on b.id = a.id
        full join tmp3 c on c.id = b.id
    order by id

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

3. 也是坑

with
    tmp1
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.id
            when b.id is not null then b.id
            when c.id is not null then c.id
            else null end id, 
       case when a.name is not null then a.name
            when b.name is not null then b.name
            when c.name is not null then c.name
            else null end name, 
       a.age, 
       b.address, 
       c.hobby
    from tmp1 a
        full join tmp2 b on b.id = a.id
        full join tmp3 c on c.id = b.id or c.id = a.id
    order by id

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

4. 麻煩的寫法

with
    tmp1
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.hobby
    from(
        select nvl(a.id,b.id) id, nvl(a.name,b.name) name, a.age, b.address
            from tmp1 a
                full join tmp2 b on b.id = a.id
    ) a1
        full join tmp3 b1 on b1.id = a1.id
            order by id