Mysql Database Table Compare Help?!

Quick question.. I have two tables in a mysql database (in the same database). Each has tens of thousands of rows, and each has a 'username' field. They techichnilly should have the same list of usernames but due to some glitches a few months ago, they don't.

What sql command can I run to compare the two tables and return the usernames that are in one but aren't in the other? Ultimately, I'd want to delete those usernames (the rows).

Any help I can get would be greatly appreciated.

Thanks!

 

I suggest using a query like this:

SELECT user1.username, users2.username
FROM `user1` 
LEFT JOIN user2 ON user1.username = user2.username
WHERE user2.username IS NULL

Code (markup):

It will return you a list of users from user1 table which don't have corresponding records in user2 table

 

Thanks!! I'm going to go try it out!

 

The tables are pretty big, so the command seems to run forever and eventually crash mysql. Is there any other way to run it? Maybe more efficiently?

 

Do the two tables being joined have an Index on the Username field?

 

No, one table has indexes on most of the columns, the other only on the ID field. Would adding a username index to that table speed up this query?

 

Okay.. I got it to run and show the usernames. Now what command do I run to delete all of those usernames (rows)?

 

I suppose

DELETE user1, users2
FROM `user1` 
LEFT JOIN user2 ON user1.username = user2.username
WHERE user2.username IS NULL

Code (markup):

But I myself had never done it. Try it on a copy first.

 

Thanks Ashine!