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
Update: 11/18/2010
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
)
Similar Posts:
February 8th, 2007 @ 4:57 pm
This is palmetto from digg.. thanks for the info about myphpadmin and mysql.
@palmetto – if you’ve already got a site and a company with PHP & MySQL hosting your site, hop on over to http://www.linein.org/blog/ and post a comment with your email address. I’d be more than happy to help. Both are really easy to setup and get started.
thanks for the help.. I will be learning now.
February 22nd, 2010 @ 6:30 am
Thanks for this little article, we have used this to compare commercial gas prices for our customers.
March 3rd, 2010 @ 2:45 pm
This just saved my butt. A winner is you!
March 16th, 2010 @ 1:17 pm
hi,
If you want more options, you can also use a third party tool, like Nob Hill’s compare :
http://www.nobhillsoft.com/NHDBCompare.aspx
April 12th, 2010 @ 1:31 pm
Thanks for that useful piece of code. It saved my time
A little smile in right upper part of the page is funny =)
May 13th, 2010 @ 1:53 pm
SELECT * FROM tblA
WHERE tblA.ID
NOT IN (
SELECT tblB.ID
FROM tblB
WHERE tblA.ID=tblB.ID
)
November 18th, 2010 @ 6:21 am
I think the sub-request method posted by JBES is much cleaner… question of opinion I guess.
Thanks all anyhow!
December 7th, 2010 @ 4:01 am
thanks this code was very helpful for me.
May 12th, 2011 @ 2:13 pm
thanks a million, you guys save my time!
June 8th, 2011 @ 12:53 pm
The statements by john and JBES only shows new inserted records. Not the UPDATEs in table_b.
August 7th, 2011 @ 10:49 am
this script only compare the id for a complete comparison it must take all fields. anyway its very interesting. thank you
November 25th, 2011 @ 5:44 am
hey i have just found a link below
its more then what you offer
http://www.technoreaders.com/2011/11/24/mysql-db-struct-comparison-open-source-project/
its still under development
but they have planned a feature to compare db tables also
January 8th, 2012 @ 6:37 am
Thanks – google did not fail me in finding this snippet (after 2 hours of ‘why isn’t this working!’ before I choose to look for answers. That select was driving me nuts and is now working like a charm. Much thanks for posting your clearly written tutorial.