连接3个包含空值的表

问题描述：

我有3个表table1的列id1有三个值'1'，'2'，'3'，table2的列id2有三个值'3'，'4'， '5'和table3的列id3具有三个值'5'，'6'，'7'我如何加入所有这三个表，并且我还希望null值被显示.pls通过下面的示例。连接3个包含空值的表

table1  |  table2  |  table3 
---------------------------------------------------- 
id1   |  id2   |  id3 
---------------------------------------------------- 
    1   |  3   |  5 
    2   |  4   |  6 
    3   |  5   |  7

这是我所期望的输出是

id1 | id2 | id3 
---------------------- 
1 | null | null 
2 | null | null 
3 | 3 | null 
null | 4 | null 
null | 5 | 5 
null | null | 6 
null | null | 7

some1请你帮我感到困惑

MySQL是否支持FULL OUTER JOIN？ – jarlh

答

你需要一个完整的外部联接，遗憾的是，MySQL没有全外连接。有一个request，但它仍然是开放的。

你有几个左侧之间做工会模仿他们加入，循环：

SELECT id1, id2, id3 
FROM 
    table1 
    LEFT JOIN table2 on table1.id1 = table2.id2 
    LEFT JOIN table3 on table2.id2 = table3.id3 
UNION 
SELECT id1, id2, id3 
FROM 
    table2 
    LEFT JOIN table3 on table2.id2 = table3.id3 
    LEFT JOIN table1 on table3.id3 = table1.id1 
UNION 
SELECT id1, id2, id3 
FROM 
    table3 
    LEFT JOIN table1 on table3.id3 = table1.id1 
    LEFT JOIN table2 on table1.id1 = table2.id2

我虽然是正确的加入，但后来我认为有超过2个表 – evilpenguin

答

使用union查询

Declare @tbl1 as table 
(
id1 int 
) 
Declare @tbl2 as table 
(
id2 int 
) 

Declare @tbl3 as table 
(
id3 int 
) 

insert into @tbl1 values(1) 
insert into @tbl1 values(2) 
insert into @tbl1 values(3) 

insert into @tbl2 values(3) 
insert into @tbl2 values(4) 
insert into @tbl2 values(5) 

insert into @tbl3 values(5) 
insert into @tbl3 values(6) 
insert into @tbl3 values(7) 

SELECT 
* 
FROM 
(
    Select 
     T1.Id1, 
     T2.Id2, 
     T3.Id3 
    FROM @tbl3 T3 
    LEFT JOIN @tbl1 T1 ON T1.id1=T3.id3 
    LEFT JOIN @tbl2 T2 ON T3.id3=T2.id2 
    UNION 
    Select 
     T1.Id1, 
     T2.Id2, 
     T3.Id3 
    FROM @tbl1 T1 
    LEFT JOIN @tbl2 T2 ON T1.id1=T2.id2 
    LEFT JOIN @tbl3 T3 ON T1.id1=T3.id3 
    Union 
    Select 
     T1.Id1, 
     T2.Id2, 
     T3.Id3 
    FROM @tbl2 T2 
    LEFT JOIN @tbl1 T1 ON T1.id1=T2.id2 
    LEFT JOIN @tbl3 T3 ON T2.id2=T3.id3 
)X 
Order by ISNULL(X.id1,9) ,ISNULL(X.id2,9),X.id3

答

该查询为您提供您所查找的输出：

select t1.id1, t2.id2, t3.id3 
from table1 t1 
left join table2 t2 
on t1.id1 = t2.id2 
left join table3 t3 
on t2.id2 = t3.id3 
UNION 
select t1.id1, t2.id2, t3.id3 
from table2 t2 
left join table3 t3 
on t2.id2 = t3.id3 
left join table1 t1 
on t2.id2 = t1.id1 
UNION 
select t1.id1, t2.id2, t3.id3 
from table3 t3 
left join table2 t2 
on t3.id3 = t2.id2 
left join table1 t1 
on t3.id3 = t1.id1; 
+------+------+------+ 
| id1 | id2 | id3 | 
+------+------+------+ 
| 3 | 3 | NULL | 
| 1 | NULL | NULL | 
| 2 | NULL | NULL | 
| NULL | 5 | 5 | 
| NULL | 4 | NULL | 
| NULL | NULL | 6 | 
| NULL | NULL | 7 | 
------+------+------+ 
7 rows in set (0.00 sec)

连接3个包含空值的表

相关推荐