Simplify subtraction between Mysql tables

Question

I would like to know how to simplify the following:

SELECT FORMAT((SUM(r.valor_receita) - (SELECT SUM(d.valor_despesa)
FROM despesas AS d WHERE d.pago = 1 AND YEAR(r.data_vencimento) <= YEAR(d.data_vencimento)
AND MONTH(r.data_vencimento) <= MONTH(d.data_vencimento) AND r.id_usuario = d.id_usuario)),2,'de_DE')
AS saldo_efetivo
FROM receitas AS r WHERE r.recebido = 1 AND YEAR(r.data_vencimento) <= '2017' AND MONTH(r.data_vencimento) <= '06' AND r.id_usuario = 1

What it currently does is add up the value of all receipts received (received = 1) and subtract with subselect that sum all expenses paid (paid = 1). I wanted to know if you can simplify this query.

Answer 1

The way the table structure is mounted I believe that there is no better way to do, but if the table structures are equal, I advise you to leave everything in the same table with a field that defines the type ("Expense" or "Revenue"), for example:

SELECT
    SUM(IF(tipo = 1,valor,valor*-1)) -- 1 - Receita / 2 - Despesa, Sendo assim faria o valor *-1 para ficar negativo
FROM tabela_contas
WHERE confirmado = 1 -- Seria se o valor foi pago ou recebido
-- E aqui o resto das suas condições