linein designs

01 Feb, 2007

Compare Two MySQL Tables

Posted by: john In: mysql

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:

13 Comments »

Comment by Palmetto

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. ;)

 
Comment by Catalyst

Thanks for this little article, we have used this to compare commercial gas prices for our customers.

 
Comment by Andrew Q

This just saved my butt. A winner is you!

 
Comment by Itamar

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

 
Comment by English

Thanks for that useful piece of code. It saved my time
A little smile in right upper part of the page is funny =)

 
Comment by JBES

SELECT * FROM tblA
WHERE tblA.ID
NOT IN (
SELECT tblB.ID
FROM tblB
WHERE tblA.ID=tblB.ID
)

 
Comment by Marcool

I think the sub-request method posted by JBES is much cleaner… question of opinion I guess.
Thanks all anyhow!

 
Comment by g.m.dinesh

thanks this code was very helpful for me.

 
Comment by gunawantw

thanks a million, you guys save my time!

 
Comment by Jake Sully

The statements by john and JBES only shows new inserted records. Not the UPDATEs in table_b.

 

this script only compare the id for a complete comparison it must take all fields. anyway its very interesting. thank you

 
Comment by jhon

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

 
Comment by Seren

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.

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

Trackback responses to this post


  • Seren: 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
  • zdenis: type your scp command with nohup at the beginning: >nohup scp... enter your password stop temporarily the command with CTRL+z put on b
  • Criz: php script >> How to get the number of hours and minutes of a two set of time like this sample. 1st set of time : 8:00 to 17:00 2nd set of


Please Support Our Friends

Banner
FreshBooks

About

My name is John Veldboom and have been in design and web programing for just over 8 years now. Started off as a hobby in high school but it has developed into a full time job now. I always tell people that if I wasn't doing this at work each day, I would be at home doing it for free. I love it!

Please visit the contact page to drop me a message.