This query UNIONs queries for matching column names from two tables, and keeps just those rows which occur once in the union. Those are the rows with unmatched data. Customise your column list { id, col1, col2, col3 ...} as desired, but usually you'll want it to start with the primary key:
SELECT MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY 1;
For convenience, encapsulate the query in a stored procedure with params for the two table names and for a comma-separated list of column names starting with the primary key:
drop procedure if exists comparetables;
delimiter go
create procedure comparetables( tbla char(64), tblb char(64), columnlist char(255) )
begin
set @sql = concat( " SELECT MIN(TableName) as TableName, ", columnlist,
" FROM ( ",
" SELECT '", tbla, "' as TableName, ", columnlist,
" FROM ", tbla,
" UNION ALL ",
" SELECT '", tblb, "' as TableName, ", columnlist,
" FROM ", tblb,
") AS tmp ",
" GROUP BY ", columnlist,
" HAVING COUNT(*) = 1 ",
" ORDER BY 1"
);
prepare stmt from @sql;
execute stmt;
drop prepare stmt;
end;
go
delimiter ;
drop table if exists a,b;
create table a(i int primary key,j int);
insert into a values(1,1),(2,2),(3,3);
create table b select * from a;
update b set j=4 where i=3;
call comparetables('a','b','i,j');
+-----------+------+------+
| TableName | i | j |
+-----------+------+------+
| a | 3 | 3 |
| b | 3 | 4 |
+-----------+------+------+
|
|