Merging two MySQL tables by multiple keys

Question

Consider two tables:

# table1
|key1|key2|key3|value1|
    1    2    3     10
    2    2    2     10
     
# table2
|key1|key2|key3|value2|
    1    2    3     20
    3    3    3     20

I want to be able to merge them by the existing keys so that I get:

|key1|key2|key3|value1|value2|
    1    2    3     10     20
    2    2    2     10   NULL
    3    3    3   NULL     20

If I use table1 LEFT JOIN table2 ON key1..3 I am going to loose the 3 3 3 row in the second table. If I do table2 LEFT JOIN table I will loose the 2 2 2 row.

Solution would be first to extract all the possible keys and then join the two tables, but this becomes too complex and I wonder if there is a simpler solution?

You want a full join which you can emulate in MySQL using a `LEFT JOIN` unioned with a `RIGHT JOIN` — Nick, Jun 22 '20 at 08:54
@Nick Imagine that one of keyX is NULL... does the thread pointed by you will solve? — Akina, Jun 22 '20 at 08:57

score 1 · Accepted Answer · answered Jun 22 '20 at 08:55

1

For example, use

SELECT key1, key2, key3, SUM(value1) value1, SUM(value2) value2
FROM ( SELECT key1, key2, key3, value1, NULL value2
       FROM table1
     UNION ALL
       SELECT key1, key2, key3, NULL, value2
       FROM table2
     ) total
GROUP BY key1, key2, key3

answered Jun 22 '20 at 08:55

Akina

39,301
5
14
25

I actually used your solution becuase the inner selects are complex and works better than the proposed fiddle. – Vojtěch Jun 22 '20 at 13:46

Merging two MySQL tables by multiple keys

1 Answers1