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
        )

You can construct the intersection manually using UNION. It's easy if you have some unique field in both tables, e.g. ID:

SELECT * FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)

UNION

SELECT * FROM T2
WHERE ID NOT IN (SELECT ID FROM T1)

If you don't have a unique value, you can still expand the above code to check for all fields instead of just the ID, and use AND to connect them (e.g. ID NOT IN(...) AND OTHER_FIELD NOT IN(...) etc)

Problem below, is to compare table before and after i do big update!.

If you use Linux, you can use commands as follow:

In terminal,

mysqldump -hlocalhost -uroot -p schema_name_here table_name_here > /home/ubuntu/database_dumps/dump_table_before_running_update.sql

mysqldump -hlocalhost -uroot -p schema_name_here table_name_here > /home/ubuntu/database_dumps/dump_table_after_running_update.sql

diff -uP /home/ubuntu/database_dumps/dump_some_table_after_running_update.sql /home/ubuntu/database_dumps/dump_table_before_running_update.sql > /home/ubuntu/database_dumps/diff.txt

You will need online tools for

• Formatting SQL exported from the dumps,

e.g http://www.dpriver.com/pp/sqlformat.htm [Not the best I've seen]

• We have diff.txt, you have to take manually the + - showing inside, which is 1 line of insert statements, that has the values.

• Do diff online for the 2 lines - & + in diff.txt, past them in online diff tool

e.g https://www.diffchecker.com [you can save and share it, and has no limit on file size!]

Note: be extra careful if its sensitive/production data!

Based on Haim's answer here's a simplified example if you're looking to compare values that exist in BOTH tables, otherwise if there's a row in one table but not the other it will also return it....

Took me a couple of hours to figure out. Here's a fully tested simply query for comparing "tbl_a" and "tbl_b"

SELECT ID, col
FROM
(
    SELECT
    tbl_a.ID, tbl_a.col FROM tbl_a
    UNION ALL
    SELECT
    tbl_b.ID, tbl_b.col FROM tbl_b
) t
WHERE ID IN (select ID from tbl_a) AND ID IN (select ID from tbl_b)
GROUP BY
ID, col
HAVING COUNT(*) = 1
 ORDER BY ID

So you need to add the extra "where in" clause:

WHERE ID IN (select ID from tbl_a) AND ID IN (select ID from tbl_b)

Also:

For ease of reading if you want to indicate the table names you can use the following:

SELECT tbl, ID, col
FROM
(
    SELECT
    tbl_a.ID, tbl_a.col, "name_to_display1" as "tbl" FROM tbl_a
    UNION ALL
    SELECT
    tbl_b.ID, tbl_b.col, "name_to_display2" as "tbl" FROM tbl_b
) t
WHERE ID IN (select ID from tbl_a) AND ID IN (select ID from tbl_b)
GROUP BY
ID, col
HAVING COUNT(*) = 1
 ORDER BY ID

you can user my own developed tool

https://github.com/hardeepvicky/MySql-Schema-Compare

I tried the above answer but found that if one table has null values and the second table has values in a column then the intersect code above does not report this fact.

select p.pcn,p.period,p.account_no,p.ytd_debit,a.ytd_debit 
-- select count(*) -- 157,283
from Plex.account_period_balance p -- 157,283/202207,148,998
join Azure.account_period_balance a  -- 157,283/202207,148,998
on p.pcn = a.pcn 
and p.period = a.period 
and p.account_no = a.account_no -- 157,283 
where p.period_display = a.period_display -- 157,283 
and p.debit = a.debit  -- 157,283 
-- and p.ytd_debit = a.ytd_debit -- 148,998
-- and p.ytd_debit != a.ytd_debit -- 0