完全联接3张桌子

本文介绍了完全联接3张桌子的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这个查询来模拟mysql中三个表的full join，

I have this query to simulate full join of three tables in mysql,

SELECT a. * , b. * , c. * FROM tbl_1 a LEFT OUTER JOIN tbl_2 b ON a.num = b.num LEFT OUTER JOIN tbl_3 c ON a.num = c.num UNION SELECT a. * , b. * , c. * FROM tbl_1 a RIGHT OUTER JOIN tbl_2 b ON a.num = b.num RIGHT OUTER JOIN tbl_3 c ON a.num = c.num

但是它生成的结果不是我期望的，我要加入的表如下，

But the result it generates is not what i am expecting, the tables that i am going to join are as follows,

Table1- num |info ---------- 1 |aaaaa 2 |bbbb 3 |ccc 3 |ccc 4 |dddd Table2- num |info ---------- 1 |aaaaa 3 |ccc 4 |dddd 5 |eeee 6 |ffff Table3- num |info ---------- 1 |aaaaa 6 |ffff 2 |bbbb

我期望的结果是，

Required result 1- num |info num |info num |info ----------- ----------- ---------- 1 |aaaaa 1 |aaaaa 1 |aaaaa 2 |bbbb NULL|NULL 2 |bbbb 3 |ccc 3 |ccc NULL|NULL 3 |ccc 3 |ccc NULL|NULL 4 |dddd 4 |dddd NULL|NULL NULL|NULL 5 |eeee NULL|NULL NULL|NULL 6 |ffff 6 |ffff

OR

Required result 2- num |info num |info num |info ----------- ----------- ---------- 1 |aaaaa 1 |aaaaa 1 |aaaaa 2 |bbbb NULL|NULL 2 |bbbb 3 |ccc 3 |ccc NULL|NULL 3 |ccc 3 |ccc NULL|NULL 4 |dddd 4 |dddd NULL|NULL NULL|NULL 6 |ffff 6 |ffff

但是我得到的是

Result i get- num |info num |info num |info ----------- ----------- ---------- 1 |aaaaa 1 |aaaaa 1 |aaaaa 2 |bbbb NULL|NULL 2 |bbbb 3 |ccc 3 |ccc NULL|NULL 3 |ccc 3 |ccc NULL|NULL 4 |dddd 4 |dddd NULL|NULL NULL|NULL NULL|NULL 6 |ffff NULL|NULL NULL|NULL 2 |bbbb

我可以知道这里出了什么问题吗，我已经坚持了2天，无法解决..我可以知道查询以获得我想要的结果吗?

May i know what is wrong over here, i've been stuck with this for 2 days and couldn't resolve..may i know the query to obtain the result i want?

非常感谢:)

推荐答案

这与John Woo的sql fiddle注释中所示相同. (原始答案包含一个错误，但现在应该可以使用)

This works as shown in John Woo's comment with sql fiddle. (the original answer contained an error but it should now work)

SELECT a. * , b. * , c. * FROM tbl_1 a LEFT OUTER JOIN tbl_2 b USING (num) LEFT OUTER JOIN tbl_3 c USING (num) UNION SELECT a. * , b. * , c. * FROM tbl_2 b LEFT OUTER JOIN tbl_1 a USING (num) LEFT OUTER JOIN tbl_3 c USING (num) UNION SELECT a. * , b. * , c. * FROM tbl_3 c LEFT OUTER JOIN tbl_1 a USING (num) LEFT OUTER JOIN tbl_2 b USING (num)