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

Question

这个问题已经有答案了:

How can I do a FULL OUTER JOIN in MySQL? (15 个回答)

已关闭 3 年前。

我有两个结构相同的 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

谢谢

Answer 1

您想要的结果是模拟的 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    |