MySQL: FULL OUTER JOIN - How do I merge one column?

Question

I have a question regarding a FULL OUTER JOIN in MySQL. I have two (or more tables):

table1      table2
id  value   id  value2
1   a       1   b
2   c       3   d
3   e       4   f

I have used this query to get my join:

SELECT * 
FROM table1
LEFT OUTER JOIN table2
ON table1.`id`=table2.`id`
UNION
SELECT * 
FROM table1
RIGHT OUTER JOIN table2
ON table1.`id`=table2.`id`

to get:

id   value1  id   value2 
1    a       1    b
2    c       NULL NULL
3    e       3    d
NULL NULL    4    f

My problem is that I don't manage to simultaneously collapse the two id columns into one column to get this:

id   value1  value2 
1    a       b
2    c       NULL
3    e       d
4    NULL    f

Any suggestions on how to do it?

Answer 1

SELECT 
COALESCE(t1.id, t2.id) as id,
t1.value1,
t2.value2
FROM table1 t1
FULL JOIN table2 t2 ON t1.id = t2.id;