CODEWITHSUNDEEP
mysqlunion
Chris
Chris

Reputation: 409

MySQL UNION tables by a single unique field

I want to UNION tables with identical columns, e.g.:

table1
id      var1    var2
1       green   red
2       blue    NULL

table2
id      var1    var2
2       NULL    pink
3       red     blue

My result should look like that:

id      var1    var2
1       green   red
2       blue    pink
3       red     blue

A "normal" UNION creates the following result:

id      var1    var2
1       green   red
2       blue    NULL
2       NULL    pink
3       red     blue

... with two id = 2 entries, as the two rows with id = 2 are not identical. However, I would like to have only one single row with the id 2.

My current workaround is as follows:

Step 1

CREATE OR REPLACE VIEW temp AS
SELECT id FROM table1
UNION
SELECT id FROM table2;

Step 2

SELECT t1.id, IFNULL(t2.var1, t3.var1) AS var1, IFNULL(t2.var2, t3.var2) AS var2
FROM temp AS t1
LEFT JOIN table1 AS t2 ON t1.id = t2.id
LEFT JOIN table2 AS t3 ON t1.id = t3.id;

I can't believe that this is the smartest way to solve the problem, as it is such a common one. And it is a real pain in the neck, when the number of tables or variables increases.

Thanks for any smart idea!

Upvotes: 1

Views: 38

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Here's one way using not exists:

select id, var1, var2
from table1 
union all
select id, var1, var2
from table2 t2
where not exists (
    select 1 
    from table1 t1 
    where t1.id = t2.id
    )

Here's another with an outer join / null check:

select id, var1, var2
from table1 
union all
select t2.id, t2.var1, t2.var2
from table2 t2
    left join table1 t1 on t2.id = t1.id
where t1.id is null

Upvotes: 1

Related Questions