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?

score 55 · Answer 1 · edited Jan 21 '15 at 20:41

55

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

edited Jan 21 '15 at 20:41

teo van kot

12,031
10
37
66

answered Jan 21 '15 at 20:21

Beau

551
1
4
2

3

Agreed.. COALESCE is what I was looking! – delux247 Sep 12 '17 at 14:45
1

Short and perfect answer! – Sagar Khatri Nov 07 '19 at 08:27

score 12 · Accepted Answer · edited Nov 29 '12 at 03:06

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

Thank you very much for the very speedy and helpful answer. That completely fixed my problem. — Mig Cervantez, Dec 11 '10 at 04:48
What if I have to do this on sqlite. It supports on LEFT JOINs ? — Sohaib, Aug 30 '14 at 18:10

score -1 · Answer 3 · answered Dec 11 '10 at 04:25

-1

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.value2
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.id = t2.id

answered Dec 11 '10 at 04:25

SOlson

32

1

This will return a 1064 - syntax error because MySQL doesn't support the [FULL OUTER JOIN](http://dev.mysql.com/doc/refman/5.0/en/join.html) -- that's why the OP didn't use it. – OMG Ponies Dec 11 '10 at 04:28
As 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: http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/ – Mig Cervantez Dec 11 '10 at 04:52

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

3 Answers3

Use:

Scripts:

Linked

Related