How to correctly get a summing table of two?

Question

MySQL

Hello, there are two signs.

ПРИХОДЫ     
валюта      приход                          
USD          1000                                  
USD	     2000
RUB	     1500
RUB           500

РАСХОДЫ
валюта     расход
USD         100
RUB         300
RUB         2000

Tell me how to get such a table from them?

ДВИЖЕНИЯ
ВАЛЮТА               ПРИХОДОБЩИЙ             РАСХОДОБЩИЙ          ОСТАТОК
USD                      3000                           100              2900
RUb                       2000                          2300             300

I understand that you need to use a join (external?) by currency, and group by it, and sum incomes and expenses, but I can’t do it in SQL
. Sorry for the oblique table.

Answer 1

All currencies are in both tables? Or some of the currencies somewhere may not be?
If there are all types of currencies in INCOMES, then you can do this:

SELECT
       p.валюта as ВАЛЮТА,
       p.tot as ПРИХОДОБЩИЙ,
       r.tot as РАСХОДОБЩИЙ,
       (p.tot - r.tot) as ОСТАТОК
FROM 
    ( SELECT SUM(приход) as tot, валюта FROM ПРИХОДЫ GROUP BY валюта ) as p
  LEFT JOIN 
      ( SELECT SUM(расход) as tot, валюта FROM РАСХОДЫ GROUP BY валюта ) as r
     ON r.валюта = p.валюта

If there may be no currencies in "INCOMES", then FULL JOIN is needed, instead of LEFT.
MySQL itself does not know how to do it, but there are several ways to implement it, for example:
UNION LEFT and RIGHT JOINs - the shortest/clearest/beautiful option, but with a large number of currencies it can start to slow down, since UNION does sorting.

SELECT
       p.валюта as ВАЛЮТА,
       p.tot as ПРИХОДОБЩИЙ,
       r.tot as РАСХОДОБЩИЙ,
       (p.tot - r.tot) as ОСТАТОК
FROM 
    ( SELECT SUM(приход) as tot, валюта FROM ПРИХОДЫ GROUP BY валюта ) as p
  LEFT JOIN 
      ( SELECT SUM(расход) as tot, валюта FROM РАСХОДЫ GROUP BY валюта ) as r
     ON r.валюта = p.валюта
UNION
SELECT
       p.валюта as ВАЛЮТА,
       p.tot as ПРИХОДОБЩИЙ,
       r.tot as РАСХОДОБЩИЙ,
       (p.tot - r.tot) as ОСТАТОК
FROM 
    ( SELECT SUM(приход) as tot, валюта FROM ПРИХОДЫ GROUP BY валюта ) as p
  RIGHT JOIN 
      ( SELECT SUM(расход) as tot, валюта FROM РАСХОДЫ GROUP BY валюта ) as r
     ON r.валюта = p.валюта

If the performance in a given place requires optimization (I advise you to think hard before answering "yes"), then you can complicate the query, making it faster.
For example, replace UNION with UNION ALL and in the second part do not select the values ​​that are in the first.