mysql 如何三个表及以上进行全连接（替代full join 两个表的可以）

Question

mysql 如何三个表及以上进行全连接（替代full join 两个表的可以）

Answer 1

有三张表，table_a, table_b, table_c
b中的a_id关联a的id，c中的b_id关联b的id
1.可以用left join union all right join实现两个表的全连接
2.再用子查询中，拼上第三个表

-- a,b,c三个表全连接
select * from(
-- a,b两表全连接
SELECT a.id as a_id, b.id as b_id FROM table_a a left join table_b b on a.id = b.a_id
union all
SELECT a.id as a_id, b.id as b_id FROM table_a a right join table_b b on a.id = b.a_id
)t1 left join (
    select * from table_c
) c on c.b_id = t1.b_id
union
select * from(
-- a,b两表全连接
SELECT a.id as a_id, b.id as b_id FROM table_a a left join table_b b on a.id = b.a_id
union all
SELECT a.id as a_id, b.id as b_id FROM table_a a right join table_b b on a.id = b.a_id
)t2 right join (
    select * from table_c
) c on c.b_id = t2.b_id;