Search code examples
mysqljoincomparisonfull-outer-join

How to compare two mysql tables side by side

I have two mysql tables which structurally are identical. What I need to do is to compare the content of both tables. There are many answers out there how to show the rows that appear in one table and not the other, but what I need is a little different. I need to output a table where each row contains the columns of both tables. Where a match is not found in one table, the columns need to contain NULL.

Although id is a primary key, id's will be different between the two tables. So for example, assume I have the following two tables.

Table 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    |
+----+---------+------------+---------+-----------+

Table 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    |
+----+---------+------------+---------+-----------+

My desired output would be:

+----------+---------------+------------+-----------+----------+---------------+------------+-----------+
| 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    |
+----------+---------------+------------+-----------+----------+---------------+------------+-----------+

Is this possible? I have tried many attempts, my latest being here. https://www.db-fiddle.com/f/6oSg88qu9N38BWpNnWTtfL/2

Thanks

Solution

  • The results that you want is a simulated FULL OUTER join.
    The way to implement the solution is the common trick to simulate the FULL OUTER join by UNION and applying a condition in the WHERE clause of the RIGHT join:

    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

    See the demo.
    Results:

    | 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    |