Compare data in two tables

-- -------------------------------------------------------------------
-- Compare data in two tables
--
-- Artful Common Queries page:
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=118
-- -------------------------------------------------------------------

-- 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 |
-- +-----------+------+------+