Finding non-duplicate records from different MySql Tables

SQL Query For DeDuplication of Records

I was searching for the solution to match two database table with same or different fields and remove the data which is present in both tables. After goggling a lot, I realize find and remove duplicate records in mysql is big issue. At last, I did not found any solution for me. So, I decide to write one. So, I am sharing my experience with you.

I have two tables as
user1 with fields  id, name, email ;
user2 with fields id, name, email ;

To remove the records present in both tables. So, I did that step by step as follows

Step 1: Find non-duplicate records form the first as well as second table.

SELECT email, count(email) FROM user1 group by email having count(email)=1
SELECT email, count(email) FROM user2 group by email having count(email)=1

This step is not useful for most of the peoples but i did this for testing only.


Step 2: Find make a union of both tables and then search these non-duplicate records.
SELECT email, count(email)
FROM
(SELECT email FROM user1 UNION ALL SELECT email FROM user2 )
as alias
WHERE
group by email having count(email)=1

Screen shot of query

This will  give the list of records which is present in only one table.

STEP 3: Export to csv, excel or create another table.

POINTS TO BE NOTED:


1. Query for tables have different fields will remain the same except you have to carefull about the selection for fields in the union all subquery  must have same fields name. For example:-
user1 with fields  id,  email, fname, lname ;
user2 with fields id, email ;
SELECT email, count(email)
FROM
(SELECT email FROM user1 UNION ALL SELECT email FROM user2 )
as alias
WHERE
group by email having count(email)=1

2. Query for same table is define in step 1

This entry was posted in Development, MySql and tagged , , , , . Bookmark the permalink.

7 Responses to Finding non-duplicate records from different MySql Tables

  1. Wonderful goods from you, man. Finding non-duplicate records from different MySql Tables | Nikesh Yadav's Blog I’ve understand your stuff previous to and you are just extremely great. I really like what you’ve acquired here, certainly like what you are saying and the way in which you say it. You make it enjoyable and you still take care of to keep it sensible. I can’t wait to read far more from you. This is really a terrific Finding non-duplicate records from different MySql Tables | Nikesh Yadav's Blog informations.

  2. Fantastic goods from you, man. Finding non-duplicate records from different MySql Tables | Nikesh Yadav's Blog I have understand your stuff previous to and you’re just too great. I really like what you’ve acquired here, certainly like what you are stating and the way in which you say it. You make it entertaining and you still take care of to keep it wise. I can’t wait to read far more from you. This is really a tremendous Finding non-duplicate records from different MySql Tables | Nikesh Yadav's Blog informations.

  3. I got what you will, thanks for putting up. Woh I am cheerful to bump this website through google. Thanks For Share Finding non-duplicate records from different MySql Tables | Nikesh Yadav's Blog.

  4. Plac zabaw says:

    cool blog and nice post.

  5. custom essay says:

    I have tried many other services but yours appeared to be the best. Lots of thanks.
    essay

Leave a Reply

Your email address will not be published. Required fields are marked *

Human Verification: In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.