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.

  • Must it be using SQL? – eroomydna Aug 8 '18 at 8:18
2

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)
  • 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 '18 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 '18 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 '18 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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