MySQL : How to merge two tables with a column?

Question

every one.

I have 2 tables.

tbl_a

id    Col_A   Col_B
1     1       2
3     3       4

tbl_b

id    Col_C   Col_D
1     100     101
2     102     103
4     104     105

I want to make a table such as:

id    Col_A   Col_B   Col_C   Col_D
1     1       2       100     101
2     0       0       102     103
3     3       4       0       0
4     0       0       104     105

I tried the SQL from How to do a FULL OUTER JOIN in MySQL?, but it wasn't the right result I want.

SQL:

SELECT * FROM tbl_a
LEFT  JOIN tbl_b ON tbl_a.id = tbl_b.id
UNION
SELECT * FROM tbl_a
RIGHT JOIN tbl_b ON tbl_a.id = tbl_b.id

Result:

id     Col A  Col B   id(1)   Col C     Col D
1      1      2       1       100       101
3      3      4       (NULL)  (NULL)    (NULL)
(NULL) (NULL) (NULL)  2       102       103
(NULL) (NULL) (NULL)  4       104       105

How can I get the correct result? Any help will be appreciated.

Thank you.

aRvi · Accepted Answer · 2020-09-19T14:10:31.153

1

This will give the desired result:

SELECT * FROM
(
    SELECT tbl_a.id, 
    tbl_a.Col_A, 
    tbl_a.Col_B, 
    COALESCE(tbl_b.Col_C, 0) AS Col_C, 
    COALESCE(tbl_b.Col_D, 0) AS Col_D
    FROM tbl_a
    LEFT  JOIN tbl_b ON tbl_a.id = tbl_b.id
    UNION
    SELECT tbl_b.id, 
    COALESCE(tbl_a.Col_A, 0) AS Col_A, 
    COALESCE(tbl_a.Col_B, 0) AS Col_B, 
    tbl_b.Col_C, 
    tbl_b.Col_D
    FROM tbl_a
    RIGHT JOIN tbl_b ON tbl_a.id = tbl_b.id
)
AS a 
ORDER BY id;

edited Sep 19 '20 at 14:10

answered Sep 19 '20 at 14:06

aRvi

2,203
1
14
30

1

[sqlfiddle](http://sqlfiddle.com/#!9/af0781/2) to this... and what is [COALESCE](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_coalesce) .... – Luuk Sep 19 '20 at 14:07
Thank you for your quick and perfect answer, aRvi. And thank for Luuk, too. You saved my time. :) – Shi J Sep 19 '20 at 14:10

score 0 · Answer 2 · answered Sep 19 '20 at 14:14

Try like this

SELECT 
    IFNULL(tbl_a.id, tbl_b.id) AS id,
    IFNULL(tbl_a.Col_A, 0) as Col_A,
    IFNULL(tbl_a.Col_B, 0) as Col_B,
    IFNULL(tbl_b.Col_C, 0) as Col_C,
    IFNULL(tbl_b.Col_D, 0) as Col_D
FROM
    tbl_a
       LEFT JOIN
           tbl_b ON tbl_a.id = tbl_b.id 
UNION 
SELECT 
     IFNULL(tbl_a.id, tbl_b.id) AS id,
     IFNULL(tbl_a.Col_A, 0) as Col_A,
     IFNULL(tbl_a.Col_B, 0) as Col_B,
     IFNULL(tbl_b.Col_C, 0) as Col_C,
     IFNULL(tbl_b.Col_D, 0) as Col_D
 FROM
     tbl_a
       RIGHT JOIN
         tbl_b ON tbl_a.id = tbl_b.id

MySQL : How to merge two tables with a column?

2 Answers2