Re: Comparing two columns on big table

[Peter Brawley]

10 million rows in each of tables `old` and 'new`, many columns, and you want a list of rows with the same id and at least one difference in other columns, is that the requirement?

If there there are 10 columns---id,c1,c2,c3,c4,c5.c6.c7.c8.c9---in tables `old` and `new`, then this query finds row non-matches ...

select min(tbl) as tablename,id,c1,c2,c3,c4,c5,c6,c7,c8,c9
from (
  select 'old' as tbl,id,c1,c2,c3,c4,c5,c6,c7,c8,c9
  from `old`
  union all
  select 'new' as tbl,id,c1,c2,c3,c4,c5,c6,c7,c8,c9
  from `new`
) u
group by id,c1,c2,c3,c4,c5,c6,c7,c8,c9
having count(*)=1
order by id;