MySQL: FULL OUTER JOIN - How do I merge one column?
27,580
Solution 1
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
Solution 2
SELECT
COALESCE(t1.id, t2.id) as id,
t1.value1,
t2.value2
FROM table1 t1
FULL JOIN table2 t2 ON t1.id = t2.id;
Author by
Mig Cervantez
Updated on July 24, 2020Comments
-
Mig Cervantez almost 4 years
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?
-
OMG Ponies over 13 yearsThis will return a 1064 - syntax error because MySQL doesn't support the FULL OUTER JOIN -- that's why the OP didn't use it.
-
Mig Cervantez over 13 yearsThank you very much for the very speedy and helpful answer. That completely fixed my problem.
-
Mig Cervantez over 13 yearsAs OMG Ponies said, unfortunately MySQL doesn't support the FULL OUTER JOIN syntax and it has to be simulated by different means. I found this helpful: xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql
-
Sohaib almost 10 yearsWhat if I have to do this on sqlite. It supports on LEFT JOINs ?
-
delux247 almost 7 yearsAgreed.. COALESCE is what I was looking!
-
Sagar Khatri over 4 yearsShort and perfect answer!