[formerly] compare two identical table data with SQL

A few days ago the interview encountered a more interesting question, is that there are two identical structure of the table A and B, but the two tables belong to different business processes, after a period of time found that the data of the two tables can not exactly match, there may be a more than B, or b more than a, or two may exist at the same time, Need to write SQL to find out the differences of two tables. At that time, I just wrote a left join way by not equal to find out the difference, today test is not possible. Here's a good way to write it back:

CREATE TABLE' test_a ' (' trade_id ' )varchar( -) not NULL,  `user_id`varchar( -)DEFAULT NULL, ' Amount 'Double DEFAULT NULL,  PRIMARY KEY(' trade_id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;CREATE TABLE' test_b ' (' trade_id ' )varchar( -) not NULL,  `user_id`varchar( -)DEFAULT NULL, ' Amount 'Double DEFAULT NULL,  PRIMARY KEY(' trade_id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

INSERT  into' Test_a ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0001','u0001', -);INSERT  into' Test_a ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0002','u0005',29.8);INSERT  into' Test_a ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0003','u0002',3050);INSERT  into' Test_a ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0004','u0003',128.4);INSERT  into' Test_a ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0005','u0004',33.7);INSERT  into' Test_b ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0001','u0001', -);INSERT  into' Test_b ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0002','u0005',29.8);INSERT  into' Test_b ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0003','u0002',3050);INSERT  into' Test_b ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0004','u0003',128.4);INSERT  into' Test_b ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0005','u0004',33.7);INSERT  into' Test_b ' (' trade_id ', 'user_id', ' Amount ')VALUES('t0006','u0006',19.6);

Select *  fromTest_awhereConcat (Concat (trade_id,user_id), amount) not inch(SelectConcat (Concat (a.trade_id,a.user_id), A.amount) fromTest_a asA Left JoinTest_b asB ona.trade_id=b.trade_id)UnionSelect *  fromTest_bwhereConcat (Concat (trade_id,user_id), amount) not inch(SelectConcat (Concat (a.trade_id,a.user_id), A.amount) fromTest_a asA Left JoinTest_b asB ona.trade_id=B.TRADE_ID)

Idea: First of all two tables of the same data to find out, and then use Table A and table B and find out the same data to make the difference, and finally the difference of the data union, may I this is not the best way, who has a better way to tell me.

[formerly] compare two identical table data with SQL