mysql - union tables by unique field

I have two tables with the same structure:

id name
1 Merry
2 Mike

and

id name
1 Mike
2 Alis

I need to union second table to first with keeping unique names, so that result is:

id name
1 Merry
2 Mike
3 Alis

Is it possible to do th开发者_StackOverflowis with MySQL query, without using php script?

This is not a join (set multiplication), this is a union (set addition).

SELECT  @r := @r + 1 AS id, name
FROM    (
        SELECT  @r := 0
        ) vars,
        (
        SELECT  name
        FROM    table1
        UNION
        SELECT  name
        FROM    table2
        ) q

This will select all names from table1 and combine those with all the names from table2 which are not in table1.

(
select * 
from table1        
)
union
(        
select * 
from table2 t2 
left join table1 t1 on t2.name = t1.name 
where t1.id is null
)

Use:

SELECT a.id,
       a.name
  FROM TABLE_A a
UNION
SELECT b.id,
       b.name
  FROM TABLE_B b

UNION will remove duplicates.

As commented, it all depends on what your 'id' means, cause in the example, it means nothing.

SELECT DISTINCT(name) FROM t1 JOIN t2 ON something

if you only want the names

SELECT SUM(something), name FROM t1 JOIN t2 ON something GROUP BY name

if you want to do some group by

SELECT DISTINCT(name) FROM t1 JOIN t2 ON t1.id = t2.id

if the id's are the same

SELECT DISTINCT COALESCE(t1.name,t2.name) FROM
mytable t1 LEFT JOIN mytable t2 ON (t1.name=t2.name);

will get you a list of unique names from the 2 tables. If you want them to get new ids (like Alis does in your desired results), that's something else and requires the answers to a couple of questions:

• do any of the names need to maintain their previous id. And if they do, which table's id should be preferred?
• why do you have 2 tables with the same structure? ie what are you trying to accomplish when you generate the unique name list?