MySQL two (almost) identical table merge the values and default unique values

Question

I have two almost identical MySQL tables besides a date column, and I would like to merge them with a select query to create a final table displaying both dates.

If there is a unique acc_id in one table -> i want to default the amount in the other to 0.

Here is an example of what i mean:

Table1:

id	acc_id	name	aug_amount
1	123	name1	100
2	456	name2	200
3	333	name3	300

Table2:

id	acc_id	name	sep_amount
1	123	name1	200
2	456	name2	300
3	444	name4	400

Merged table:

acc_id	name	aug_amount	sep_amount
123	name1	100	200
456	name2	200	300
333	name3	300	0
444	name4	0	400

Is there a way i can achieve this with a singular SQL query? I've been playing around for a while but i cant seem to crack it.

Help appreciated! Thanks for reading.

Answer 1

Ignoring the PK column, here is one solution where we union the two tables and then select from it:

select acc_id, name, sum(aug_amount) as aug_amount, sum(sep_amount) as sep_amount
from (
  select acc_id, name, aug_amount, 0 as sep_amount
  from table1
  union
  select acc_id, name, 0, sep_amount
  from table2
)z
group by acc_id, name
order by name;

acc_id	name	aug_amount	sep_amount
123	name1	100	200
456	name2	200	300
333	name3	300	0
444	name4	0	400

---

View on DB Fiddle