MySQL – How to Show Differences Between Two Tables

MySQL

I have two databases, each containing a 'settings' table with the same structure like this. (There are hundreds of rows, but this is an example.)

db1.settings                              db2.settings
+-------------------+-----------+        +-------------------+-----------+
| param             | value     |        | param             | value     |
+-------------------+-----------+        +-------------------+-----------+
| MinimalShotLength | 50        |        | MinimalShotLength | 30        |
| OneTemplate       | 0         |        | OneTemplate       | 0         |
| MP4Type           | Universal |        | MP4Type           | Universal |
| Mixdown002        | Default   |        | VideoDevice       | Aja       |
+-------------------+-----------+        +-------------------+-----------+

db1 may contain rows that are missing in db2 and vice-versa. The rows are not in the same order.

I want to create a new table showing only the differences between the two like this:

+-------------------+-----------+------+
| param             | db1       | db2  |
+-------------------+-----------+------+
| MinimalShotLength | 50        | 30   |
| Mixdown002        | Default   | NULL |
| VideoDevice       | NULL      | Aja  |
+-------------------+-----------+------+

Is this possible in MySQL or do I need to do it in PHP? The closest I could find was this answer, but that puts each param on a separate row so it's harder to read the output.

Best Answer

MySQL knows nothing about FULL OUTER JOIN, so

SELECT t0.param, t1.value, t2.value
FROM (SELECT param from db1.settings
          UNION
      SELECT param from db2.settings) t0
LEFT JOIN db1.settings t1 ON t0.param = t1.param
LEFT JOIN db2.settings t2 ON t0.param = t2.param
-- remove duplicates if not need in
WHERE NOT (t1.value <=> t2.value)
Related Question