Mysql: Compare Differences Between Two Tables

MySQL: Compare differences between two tables

INTERSECT needs to be emulated in MySQL:

SELECT  'robot' AS `set`, r.*
FROM    robot r
WHERE   ROW(r.col1, r.col2, …) NOT IN
        (
        SELECT  col1, col2, ...
        FROM    tbd_robot
        )
UNION ALL
SELECT  'tbd_robot' AS `set`, t.*
FROM    tbd_robot t
WHERE   ROW(t.col1, t.col2, …) NOT IN
        (
        SELECT  col1, col2, ...
        FROM    robot
        )

Mysql compare two tables and display only the difference

Use NOT EXISTS

select * 
from table2 A
Where Not exists (select 1 from table1 B Where A.ID = B.value)

Or LEFT OUTER JOIN

select *
from table2 A 
LEFT OUTER JOIN table1 B 
on A.ID = B.value
Where B.value IS NULL

Compare values in two tables and calculate the difference of them

Use sub-query & do further aggregation :

SELECT Product, SUM(logistics),  SUM(Sales),
       SUM(logistics) - SUM(Sales)
FROM (SELECT product, 0 AS logistics, SUM(sales_volume) AS Sales
      FROM sales
      WHERE KPI = 'sold'
      GROUP BY Product
      UNION ALL
      SELECT product, SUM(quantity), 0 
      FROM logistics
      WHERE KPI = 'outbound'
      GROUP BY Product
     ) t
GROUP BY Product;

How to compare two tables and show every difference

Use FULL OUTER JOIN

SELECT *
FROM   Table1 t1
       FULL OUTER JOIN Table2 t2
                    ON t1.col = t2.col
WHERE  t1.col IS NULL
        OR t2.col IS NULL 

If your DBMS does not support FULL OUTER JOIN then

SELECT *
FROM   Table1 t1
       LEFT JOIN Table2 t2
                    ON t1.col = t2.col
WHERE  t2.col IS NULL
Union all
SELECT *
FROM   Table1 t1
       Right JOIN Table2 t2
                    ON t1.col = t2.col
WHERE  t1.col IS NULL

Select and compare 2 tables from different databases

SELECT * FROM DBOLD.items where mainKey not in (SELECT mainkey FROM DBNEW.items )

this will give data missing in DBOLD

---

Related Topics