完全联接3张桌子 [英] Full join for 3 tables

问题描述

我有这个查询来模拟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)

这篇关于完全联接3张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！