Compare values of two database tables -> MySQL

Question

I looked through various topics here on StackOverflow but I was unable to find the right solution for my problem. Here is what I´ve got:

I have two database tables. One table should be some kind of a reference model and looks similar to this example:

+----+----------------+----------------+----------------+-----------------+
| ID |     FIELD1     |     FIELD2     |     FIELD3     |     FIELD 4     |
+----+----------------+----------------+----------------+-----------------+
|  1 | Value1_Field_1 | Value1_Field_2 | Value1_Field_3 | Value1_Field_4  |
|  2 | Value2_Field_1 | Value2_Field_2 | Value2_Field_3 | Value2_Field_4  |
|  3 | Value3_Field_1 | Value3_Field_2 | Value3_Field_3 | Value3_Field_4  |
|  4 | Value4_Field_1 | Value4_Field_2 | Value4_Field_3 | Value4_Field_4  |
|  5 | Value5_Field_1 | Value5_Field_2 | Value5_Field_3 | Value5_Field_4  |
+----+----------------+----------------+----------------+-----------------+

Now I enter new data to a second table. Those data can have the same values and also the same amount of rows. But it can also happen, that some data have more rows or the values inside the rows are different. Here is another table example where I have one row more and two values are different:

+----+----------------+-----------------+-----------------+----------------+
| ID |     FIELD1     |     FIELD2      |     FIELD3      |    FIELD 4     |
+----+----------------+-----------------+-----------------+----------------+
|  1 | Value1_Field_1 | Value1_Field_2  | Value1_Field_3  | Value1_Field_4 |
|  2 | Value2_Field_1 | Value2_Field_2  | Value2_Field_3  | Value2_Field_4 |
|  3 | Value3_Field_1 | Value3_Field_2  | Value3_NEWVALUE | Value3_Field_4 |
|  4 | Value4_Field_1 | Value4_Field_2  | Value4_Field_3  | Value4_Field_4 |
|  5 | Value5_Field_1 | Value5_NEWVALUE | Value5_Field_3  | Value5_Field_4 |
|  6 | Value6_Field_1 | Value6_Field_2  | Value6_Field_3  | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+

I am looking for a SQL-Statement which compares those two tables and list all different records. In my example above the SQL-Statement should retourn those informations:

+----+----------------+-----------------+-----------------+----------------+
| ID |     FIELD1     |     FIELD2      |     FIELD3      |    FIELD 4     |
+----+----------------+-----------------+-----------------+----------------+
|  3 |                |                 | Value3_NEWVALUE |                |
|  5 |                | Value5_NEWVALUE |                 |                |
|  6 | Value6_Field_1 | Value6_Field_2  | Value6_Field_3  | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+

Here is what I´ve tried so far:

SELECT distinct FIELD1, FIELD2, FIELD3, FIELD4 from table_references
union
SELECT distinct FIELD1, FIELD2, FIELD3, FIELD4  from table_new_data

That statement returns all rows but only once. This is not what I am looking for. I also tried it with this code:

SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM (
SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM table_references
UNION ALL
SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM table_new_data
) tbl
GROUP BY FIELD1
HAVING count(*) = 1
ORDER BY FIELD1

That code only returns row 6 but does not show me the new values inside ID 3 & 5.

Any help would be really appreciated. Thanks in advance!

EDIT: According to @Madhur Bhaiya solution I made a mistake. The output should look like this:

+----+----------------+-----------------+-----------------+----------------+
| ID |     FIELD1     |     FIELD2      |     FIELD3      |    FIELD 4     |
+----+----------------+-----------------+-----------------+----------------+
|  3 | Value3_Field1  | Value3_Field2   | Value3_NEWVALUE | Value3_Field4  |
|  5 | Value5_Field1  | Value5_NEWVALUE | Value5_Field3   | Value5_Field4  |
|  6 | Value6_Field_1 | Value6_Field_2  | Value6_Field_3  | Value6_Field_4 |
+----+----------------+-----------------+-----------------+----------------+

So I also need all values from the affected row where a value is different.

Madhur Bhaiya · Accepted Answer · 2018-12-19 13:43:18Z

2

We can use LEFT JOIN to compare the two tables and then use conditional functions like If() to get values accordingly:

Query

SELECT tnew.*
FROM table_new_data AS tnew
LEFT JOIN table_references AS told
  ON told.ID = tnew.ID
WHERE (told.ID IS NOT NULL AND 
       (tnew.FIELD1 <> told.FIELD1 OR 
        tnew.FIELD2 <> told.FIELD2 OR 
        tnew.FIELD3 <> told.FIELD3 OR 
        tnew.FIELD4 <> told.FIELD4)
      ) OR 
      told.ID IS NULL;

Result

| ID  | FIELD1         | FIELD2          | FIELD3          | FIELD4         |
| --- | -------------- | --------------- | --------------- | -------------- |
| 3   | Value3_Field_1 | Value3_Field_2  | Value3_NEWVALUE | Value3_Field_4 |
| 5   | Value5_Field_1 | Value5_NEWVALUE | Value5_Field_3  | Value5_Field_4 |
| 6   | Value6_Field_1 | Value6_Field_2  | Value6_Field_3  | Value6_Field_4 |

View on DB Fiddle

edited Dec 19, 2018 at 13:43

answered Dec 19, 2018 at 13:21

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Christoph C. Over a year ago

Thank you very much. Your code works and I appreciate your help. I just edited my post because the output should be a little bit different that I what I have posted. I also need to see the other values inside the row where a value is different. Can you change your code, so that the output will list all values inside the rows where a value is different. Thank you so much.

Christoph C. Over a year ago

awesome. Thank you very much. This is exactly what I was looking for. Will upvote and accept as answer. Have a nice day.

J. Conner · Accepted Answer · 2018-12-19 13:19:35Z

0

I believe instead of a Union Clause, you are looking for for the Intersect Clause. Union will report everything in the two tables, once(per the distict modifier). Intersect will only report the commonalities.

answered Dec 19, 2018 at 13:19

J. Conner

33 bronze badges

4 Comments

Christoph C. Over a year ago

Well I am not pretty sure if you are right cause what I have read is that the intersect clause shows me all commonalities of those two tables. But I am looking for the differences between those two tables. Or did I misunderstood something?

Madhur Bhaiya Over a year ago

There is no INTERSECT clause in MySQL. @ChristophC.

Salman Arshad Over a year ago

Actually, EXCEPT would be more appropriate.

J. Conner Over a year ago

INTERSECT is where they intersect(or what they have in common), union is everything in the two tables. But that only applies to SQL, not MySQl as I am only now seeing the question was written for because as you mentioned there is no intersect in MySQL only SQL

Collectives™ on Stack Overflow

Compare values of two database tables -> MySQL

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related