PostgreSQL：关联子查询中的COALESCE为何未生效？

你遇到的问题核心在于FULL JOIN的特性和子查询中COALESCE的作用范围：

• 子查询里的COALESCE(SUM(amount), 0)仅处理该子查询内部分组后无数据的场景——比如某个用户没有任何发送记录，SUM(amount)返回NULL，这时候转成0。
• 但FULL JOIN会产生两种单边匹配的情况：
  1. 仅t1有匹配行（用户只有发送记录，无接收记录）：此时t2的所有字段（包括received）都是NULL
  2. 仅t2有匹配行（用户只有接收记录，无发送记录）：此时t1的所有字段（包括sent）都是NULL
• 当你直接用received - sent计算时，只要其中一个值是NULL，整个表达式结果就是NULL，这就是balance出现NULL的原因。

修正后的SQL需要在外部计算balance时，再次对sent和received做NULL处理：

SELECT
    COALESCE(t1.cid, t2.cid) as currency_id,
    COALESCE(t1.sid, t2.rid) as user_id,
    COALESCE(received, 0) - COALESCE(sent, 0) 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)

另外补充一个优化思路：可以把两个子查询合并成一个，用条件聚合减少JOIN操作，效率更高：

SELECT
    currency_id,
    user_id,
    COALESCE(SUM(CASE WHEN role = 'receiver' THEN amount END), 0) 
    - COALESCE(SUM(CASE WHEN role = 'sender' THEN amount END), 0) AS balance
FROM (
    SELECT
        currency_id,
        senderId AS user_id,
        'sender' AS role,
        amount
    FROM public.transfer
    UNION ALL
    SELECT
        currency_id,
        recepientId AS user_id,
        'receiver' AS role,
        amount
    FROM public.transfer
) AS combined
GROUP BY currency_id, user_id

内容的提问来源于stack exchange，提问作者sev