MySQL: FULL OUTER JOIN - How do I merge one column?
SELECT COALESCE(t1.id, t2.id) as id,t1.value1,t2.value2FROM table1 t1FULL JOIN table2 t2 ON t1.id = t2.id;
Use:
SELECT t1.id, t1.value, t2.value2 FROM table1 t1 LEFT JOIN table2 t2 ON t2.id = t1.idUNION SELECT t2.id, t1.value, t2.value2 FROM TABLE1 t1RIGHT 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
For what I think you are trying to do, I would suggest using a FULL OUTER JOIN instead:
SELECT ISNULL(t1.id, t2.id) AS id, t1.value1, t2.value2FROM table1 t1FULL OUTER JOIN table2 t2 ON t1.id = t2.id