1

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.

1
  • Must it be using SQL?
    – eroomydna
    Aug 8, 2018 at 8:18

1 Answer 1

3

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)
3
  • Thanks, this is a great start! But in both examples, it does not remove rows where the param/value is equal between the two tables.
    – Elliott B
    Aug 8, 2018 at 8:10
  • it does not remove rows where the param/value is equal between the two tables. Well, I'll edit... wait. Done.
    – Akina
    Aug 8, 2018 at 8:12
  • Excellent, thank you! One small detail is I will edit the first line t1.value as db1, t2.value as db2 to show column names
    – Elliott B
    Aug 8, 2018 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.