MySQL Forums
Forum List  »  Optimizer & Parser

Re: Comparing two columns on big table
Posted by: Peter Brawley
Date: March 21, 2019 04:24PM

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;

Options: ReplyQuote

Written By
Re: Comparing two columns on big table
March 21, 2019 04:24PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.