Compare Two MySQL Tables
The other day I needed to compare two tables and see what data was in one but not in the other.
table_a is my main table that I want to update and table_b is my storage table that I have my updates in. The query below will return all records that are in table_b, but no in table_a. Then you can take those results and use them to INSERT the missing records.
SELECT table_b.* FROM table_b LEFT JOIN table_a ON table_b.id = table_a.id WHERE table_a.item_id IS NULL
JBES commented on another way to do the same thing. I tried it on two tables with over 4 million records and the difference in performance was very minor. But if you notice any huge difference, please let us know in the comments. (this method is a little cleaner IMHO)
SELECT * FROM tblA WHERE tblA.ID NOT IN ( SELECT tblB.ID FROM tblB WHERE tblA.ID=tblB.ID )