How do I compare two tables data?

Hi I do have an old tab开发者_运维技巧le and new table with same index/data. TABLE1 and TABLE2

but TABLE1 has got more data than TABLE2. this was maintained by some one and I dont know how this happened. so my question is how do I compare these two table and find which data is TABLE2 missing?? there is almost 200000 datas there so manually doing is not possible...

in PHP:

http://us.php.net/manual/en/function.array-diff.php

in SQL:

SELECT * FROM TABLE1 WHERE id {NOT} IN ( SELECT id FROM TABLE2 )

depending on criteria of comparison

Solution without nested query:

select TABLE1.id from TABLE1 left join TABLE2 on TABLE1.id = TABLE2.id where TABLE2.id is null

Did you mean something like this:

SELECT * FROM TABLE1 t1 WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE t1.id == t2.id)

By same index I am hoping you mean they share a primary key?

SELECT * FROM TABLE1 WHERE username NOT IN (SELECT username FROM TABLE2)