mysql - 如何并排比较两个mysql表

This question already has answers here:

How to do a FULL OUTER JOIN in MySQL?
                                
                                    （14个回答）
                                
                        
                                4个月前关闭。
            
                    
我有两个在结构上相同的mysql表。我需要做的是比较两个表的内容。那里有很多答案，如何显示出现在一个表中而不是另一个表中的行，但是我需要的有点不同。我需要输出一个表，其中每一行都包含两个表的列。如果在一个表中找不到匹配项，则这些列需要包含NULL。

尽管id是主键，但两个表之间的id会有所不同。因此，例如，假设我有以下两个表。

表格1

+----+---------+------------+---------+-----------+
| id | alias   | short_name | country | role      |
+----+---------+------------+---------+-----------+
| 1  | alias_1 | Product 1  | USA     | retail    |
+----+---------+------------+---------+-----------+
| 2  | alias_1 | Product 1  | USA     | corporate |
+----+---------+------------+---------+-----------+
| 3  | alias_1 | Product 1  | POL     | retail    |
+----+---------+------------+---------+-----------+
| 4  | alias_1 | Product 1  | BEL     | corporate |
+----+---------+------------+---------+-----------+
| 5  | alias_2 | Product 2  | USA     | retail    |
+----+---------+------------+---------+-----------+
| 6  | alias_2 | Product 2  | BEL     | corporate |
+----+---------+------------+---------+-----------+
| 7  | alias_2 | Product 2  | BEL     | retail    |
+----+---------+------------+---------+-----------+

表2

+----+---------+------------+---------+-----------+
| id | alias   | short_name | country | role      |
+----+---------+------------+---------+-----------+
| 10 | alias_1 | Product 1  | USA     | retail    |
+----+---------+------------+---------+-----------+
| 13 | alias_1 | Product 1  | USA     | corporate |
+----+---------+------------+---------+-----------+
| 14 | alias_1 | Product 1  | POL     | corporate |
+----+---------+------------+---------+-----------+
| 16 | alias_1 | Product 1  | BEL     | retail    |
+----+---------+------------+---------+-----------+
| 17 | alias_2 | Product 2  | USA     | retail    |
+----+---------+------------+---------+-----------+
| 22 | alias_2 | Product 2  | BEL     | corporate |
+----+---------+------------+---------+-----------+
| 25 | alias_2 | Product 2  | BEL     | retail    |
+----+---------+------------+---------+-----------+
| 22 | alias_3 | Product 3  | BEL     | corporate |
+----+---------+------------+---------+-----------+
| 25 | alias_3 | Product 3  | BEL     | retail    |
+----+---------+------------+---------+-----------+

我想要的输出将是：

+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| t1_alias | t1_short_name | t1_country | t1_role   | t2_alias | t2_short_name | t2_country | t2_role   |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| alias_1  | Product 1     | USA        | retail    | alias_1  | Product 1     | USA        | retail    |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| alias_1  | Product 1     | USA        | corporate | alias_1  | Product 1     | USA        | corporate |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| alias_1  | Product 1     | POL        | retail    | <NULL>   | <NULL>        | <NULL>     |  <NULL>   |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| alias_1  | Product 1     | BEL        | corporate | <NULL>   | <NULL>        | <NULL>     |  <NULL>   |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| alias_2  | Product 2     | USA        | retail    | alias_2  | Product 2     | USA        | retail    |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| alias_2  | Product 2     | BEL        | corporate | alias_2  | Product 2     | BEL        | corporate |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| alias_2  | Product 2     | BEL        | retail    | alias_2  | Product 2     | BEL        | retail    |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| <NULL>   | <NULL>        | <NULL>     |  <NULL>   | alias_1  | Product 1     | POL        | corporate |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| <NULL>   | <NULL>        | <NULL>     |  <NULL>   | alias_1  | Product 1     | BEL        | retail    |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| <NULL>   | <NULL>        | <NULL>     |  <NULL>   | alias_3  | Product 3     | BEL        | corporate |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| <NULL>   | <NULL>        | <NULL>     |  <NULL>   | alias_3  | Product 3     | BEL        | retail    |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+

这可能吗？我已经尝试了许多尝试，最近一次是在这里。 https://www.db-fiddle.com/f/6oSg88qu9N38BWpNnWTtfL/2

谢谢

最佳答案

所需的结果是模拟的FULL OUTER连接。
解决方案的实现方法是通过FULL OUTER模拟UNION联接并在WHERE联接的RIGHT子句中应用条件的常见技巧：

SELECT
  table_1.alias t1_alias, table_1.short_name t1_short_name,
  table_1.country t1_country, table_1.role t1_role,
  table_2.alias t2_alias, table_2.short_name t2_short_name,
  table_2.country t2_country, table_2.role t2_role
FROM table_1
LEFT JOIN table_2
ON table_1.alias = table_2.alias AND table_1.short_name = table_2.short_name
AND table_1.country = table_2.country
AND table_1.role = table_2.role
UNION ALL
SELECT
  table_1.alias t1_alias, table_1.short_name t1_short_name,
  table_1.country t1_country, table_1.role t1_role,
  table_2.alias t2_alias, table_2.short_name t2_short_name,
  table_2.country t2_country, table_2.role t2_role
FROM table_1
RIGHT JOIN table_2
ON table_1.alias = table_2.alias AND table_1.short_name = table_2.short_name
AND table_1.country = table_2.country
AND table_1.role = table_2.role
WHERE table_1.alias IS NULL
ORDER BY t1_alias IS NULL, t1_alias

请参见demo。
结果：

| t1_alias | t1_short_name | t1_country | t1_role   | t2_alias | t2_short_name | t2_country | t2_role   |
| -------- | ------------- | ---------- | --------- | -------- | ------------- | ---------- | --------- |
| alias_1  | Product 1     | USA        | retail    | alias_1  | Product 1     | USA        | retail    |
| alias_1  | Product 1     | USA        | corporate | alias_1  | Product 1     | USA        | corporate |
| alias_1  | Product 1     | POL        | retail    |          |               |            |           |
| alias_1  | Product 1     | BEL        | corporate |          |               |            |           |
| alias_2  | Product 2     | USA        | retail    | alias_2  | Product 2     | USA        | retail    |
| alias_2  | Product 2     | BEL        | corporate | alias_2  | Product 2     | BEL        | corporate |
| alias_2  | Product 2     | BEL        | retail    | alias_2  | Product 2     | BEL        | retail    |
|          |               |            |           | alias_1  | Product 1     | POL        | corporate |
|          |               |            |           | alias_1  | Product 1     | BEL        | retail    |
|          |               |            |           | alias_3  | Product 3     | BEL        | corporate |
|          |               |            |           | alias_3  | Product 3     | BEL        | retail    |