在使用JOINS-mysql的两个表的UNION上出现重复列错误

Question

我有两个表，如下所示：

CREATE TABLE NameWithAddress
( 
Name                    VARCHAR(255),
Address                 VARCHAR(255)
); 
CREATE TABLE NamesWithCountry
( 
Name                   VARCHAR(255),
Country                VARCHAR(255)
);

我想合并两个表，这样名称上匹配的行就会连接起来，但两个表中不匹配的行仍然包含在查询中

我有一个查询，返回我想要的结果：

SELECT *
FROM `NameWithAddress`
LEFT OUTER JOIN `NamesWithCountry` ON `NameWithAddress`.`Name` = `NamesWithCountry`.`Name`
UNION SELECT *
FROM `NameWithAddress`
RIGHT OUTER JOIN `NamesWithCountry` ON `NameWithAddress`.`Name` = `NamesWithCountry`.`Name`
WHERE `NameWithAddress`.`Name` IS NULL

但是，当我尝试选择这个查询的结果时，我会得到一个重复的列错误。这就是我执行SELECT：的方式

SELECT T.* FROM (
SELECT *
FROM `NameWithAddress`
LEFT OUTER JOIN `NamesWithCountry` ON `NameWithAddress`.`Name` = `NamesWithCountry`.`Name`
UNION SELECT *
FROM `NameWithAddress`
RIGHT OUTER JOIN `NamesWithCountry` ON `NameWithAddress`.`Name` = `NamesWithCountry`.`Name`
WHERE `NameWithAddress`.`Name` IS NULL) T

Answer 1

通常，您会使用full join来执行此操作，但MySQL不支持FULL JOIN。我建议：

SELECT n.name, nwa.address, nwc.country
FROM (SELECT Name
FROM NameWithAddress
UNION   -- on purpose to remove duplicates
SELECT Name
FROM NamesWithCountry
) n LEFT JOIN
NameWithAddress nwa
ON nwa.name = n.name LEFT JOIN
NamesWithCountry nwc
ON nwc.name = n.name;

如果您知道名称在任何一个表中都不会重复，那么：

select name, max(address) as address, max(country) as country
from ((select name, address, null as country
from namewithaddress
) union all
(select name, null as address, country
from namewithcountry
)
) n
group by name;

我强烈反对将union与left join和right join一起使用。在某些情况下，这在某种程度上近似于full join。然而，它比这些方法更需要性能，并且与实际full join的细微差异可能很难弄清楚(并纠正(。

Answer 2

您需要重命名其中一个Name列或省略它们：

SELECT T.* FROM (
SELECT `NameWithAddress`.*, `NamesWithCountry`.`Country`
FROM `NameWithAddress`
LEFT OUTER JOIN `NamesWithCountry` ON `NameWithAddress`.`Name` = `NamesWithCountry`.`Name`
UNION SELECT `NameWithAddress`.*, `NamesWithCountry`.`Country`
FROM `NameWithAddress`
RIGHT OUTER JOIN `NamesWithCountry` ON `NameWithAddress`.`Name` = `NamesWithCountry`.`Name`
WHERE `NameWithAddress`.`Name` IS NULL) T