Merging two tables without duplicating

Question

I need to merge two user tables to one without duplicates.

for example : in my database i have tables en_user and kn_user and there are few users exists in both so i when i run an query to merge kn_user with en_user then unique users present in kn_user should be copied to en_user where users present in both should not be merged.

for better understanding my tables are

en_users table

**id    name** 
1     Rakesh
2     Deepu
3     sudha
4     sandeep
5     Anil

kn_user table

**id    name** 
1     Ashwini
2     Rakesh
3     sudha
4     sunil
5     Anil     

so when in merge kn_users with en_users in need this output

en_users table

**id    name** 
1     Rakesh
2     Deepu
3     sudha
4     sandeep
5     Anil
6     Ashwini
7     Sunil

Answer 1

Here is a query that does what you want:

select (@rn := @rn + 1) as id, name
from ((select id, name, 1 as priority
       from en_users
      ) union all
      (select id, name, 2
       from kn_users k
       where not exists (select 1 from en_users e where e.name = k.name)
      )
     ) ek cross join
     (select @rn := 0) params
order by priority, id;

The logic in the subquery is to take everything from one table and then only non-matching rows from the second table. The example is based on name.

The query is careful to assign the final id based on the example in the question -- first assigning ids from the "en" table then the "kn" table, and in order by ids in the two tables.