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; 

Answer 2

Use:

    SELECT t1.id,            t1.value,            t2.value2        FROM table1 t1  LEFT JOIN table2 t2 ON t2.id = t1.id UNION     SELECT t2.id,            t1.value,            t2.value2       FROM TABLE1 t1 RIGHT JOIN TABLE2 t2 ON t2.id = t1.id 

The UNION operator removes row/record duplicates, so you have to define/list the columns appropriately.

Scripts:

DROP TABLE IF EXISTS `example`.`table1`; CREATE TABLE  `example`.`table1` (   `id` int(10) unsigned NOT NULL default '0',   `value` varchar(45) NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  INSERT INTO table1 VALUES (1, 'a'), (2, 'c'), (3, 'e');  DROP TABLE IF EXISTS `example`.`table2`; CREATE TABLE  `example`.`table2` (   `id` int(10) unsigned NOT NULL default '0',   `value2` varchar(45) NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  INSERT INTO table2 VALUES (1, 'b'), (3, 'd'), (4, 'f'); 

Edit: Fixed line above