0

I am selecting some columns from 2 tables I am joining - why is the coalesce in the underlying tables not having an effect?

SELECT
    COALESCE(t1.cid, t2.cid) as currency_id,
    COALESCE(t1.sid, t2.rid) as user_id,
    (received) - (sent) as balance
FROM
    (
        SELECT
            "currency_id" as cid,
            "senderId" as sid,
            COALESCE(SUM(amount), 0) as sent
        FROM
            public.transfer
        GROUP BY
            "cid",
            "sid"
    ) t1
    FULL JOIN
    (
        SELECT
            "currency_id" as cid,
            "recepientId" as rid,
            COALESCE(SUM(amount),0) as received
        FROM
            public.transfer
        GROUP BY
            cid,
            rid
    ) t2
        ON (t1.cid = t2.cid AND t1.sid = t2.rid)

The balance column has nulls in it even though I would expect sent and received to never be null because of the coalesce. Further - if I change the statement to calculate the balance to COALESCE(received, 0) - COALESCE(sent, 0) as balance everything works as expected

sev
  • 1,500
  • 17
  • 45
  • The NULLs are likely originating from the fact you're using `FULL OUTER JOIN`. – Dai Oct 04 '22 at 07:39
  • @Dai I fixed the typos - I think you are correct, the NULLS are from the FULL OUTER JOIN thanks for the help. If you like you can write it as an answer so I can accept it – sev Oct 04 '22 at 07:46

0 Answers0