CODEWITHSUNDEEP
mysql
jia Jimmy
jia Jimmy

Reputation: 1848

Is there better way to get the difference between two table with same fields?

I have two tables as below:

table1:

+----+----------+-------+
| id | order_id | price |
+----+----------+-------+
|  1 |     1024 |    20 |
|  2 |     1025 |    30 |
|  3 |     1026 |    35 |
|  4 |     1027 |    45 |
+----+----------+-------+

table2

+----+----------+-------+------+
| id | order_id | price | name |
+----+----------+-------+------+
|  1 |     1024 |    20 | a    |
|  2 |     1025 |    30 | b    |
|  3 |     1026 |    35 | c    |
|  4 |     1027 |    40 | d    |
+----+----------+-------+------+

What I want to do is just camparing fields order_id and price, and get the different content when order_id = 1027


Here is my humble opinion:


SELECT * FROM (

SELECT order_id, price FROM table1 

UNION ALL

SELECT order_id, price FROM table2

) t

GROUP BY order_id, price 

HAVING COUNT(*) = 1


# result 

+----------+-------+
| order_id | price |
+----------+-------+
|     1027 |    40 |
|     1027 |    45 |
+----------+-------+

Is there any better way to get it.

Any commentary is very welcome. great thanks.

Upvotes: 0

Views: 40

Answers (2)

Nick
Nick

Reputation: 147146

Another alternative would be to use a JOIN to find non-matching prices:

SELECT t1.order_id, t1.price AS table1_price, t2.price AS table2_price
FROM table1 t1
JOIN table2 t2 ON t2.order_id = t1.order_id AND t2.price != t1.price

Output:

order_id    table1_price    table2_price
1027        45              40

Demo on dbfiddle

If you also want to capture rows which exist in one table but not the other, then you will need a FULL OUTER JOIN, which MySQL doesn't support, and must be emulated using a UNION of a LEFT JOIN and a RIGHT JOIN:

SELECT * 
FROM (SELECT t1.order_id AS order_id, t1.price AS table1_price, t2.price AS table2_price
      FROM table1 t1
      LEFT JOIN table2 t2 ON t2.order_id = t1.order_id
      UNION
      SELECT t2.order_id, t1.price AS table1_price, t2.price AS table2_price
      FROM table1 t1
      RIGHT JOIN table2 t2 ON t2.order_id = t1.order_id) t
WHERE table1_price != table2_price OR
      table1_price IS NULL OR
      table2_price IS NULL

Output:

order_id    table1_price    table2_price
1027        45              40
1028        50              null
1029        null            45

Demo on dbfiddle

Upvotes: 1

Ali
Ali

Reputation: 67

You can use left join to get the values

SELECT table1.order_id, table1.price FROM table1 LEFT JOIN table2 ON table2.order_id = table1.order_id AND table2.price != table1.price

Upvotes: 0

Related Questions