在FULL OUTER JOIN操作中能否避免使用COALESCE函数？

可以通过UNION ALL + 条件聚合的方式替代FULL OUTER JOIN + 多次COALESCE的写法，减少重复语法调用，实现逻辑更简洁：

基础改写版本 #

WITH Combined AS (
    -- 合并两张表的原始数据并标记来源
    SELECT id, amount, 'd1' AS source FROM table1
    UNION ALL
    SELECT id, amount, 'd2' AS source FROM table2
),
Aggregated AS (
    SELECT
        id,
        -- 按来源分别汇总金额，空值转为0
        COALESCE(SUM(CASE WHEN source = 'd1' THEN amount END), 0) AS d1_amount,
        COALESCE(SUM(CASE WHEN source = 'd2' THEN amount END), 0) AS d2_amount
    FROM Combined
    GROUP BY id
)
SELECT
    id,
    d1_amount,
    d2_amount,
    d1_amount - d2_amount AS delta
FROM Aggregated
-- 过滤金额差异≥5的记录
WHERE ABS(d1_amount - d2_amount) >= 5
ORDER BY id

数据库特性优化版本（如PostgreSQL支持FILTER子句） #

如果你的数据库支持FILTER条件聚合语法，还能进一步简化CASE WHEN的写法：

WITH Aggregated AS (
    SELECT
        id,
        COALESCE(SUM(amount) FILTER (WHERE source = 'd1'), 0) AS d1_amount,
        COALESCE(SUM(amount) FILTER (WHERE source = 'd2'), 0) AS d2_amount
    FROM (
        SELECT id, amount, 'd1' AS source FROM table1
        UNION ALL
        SELECT id, amount, 'd2' AS source FROM table2
    ) Combined
    GROUP BY id
)
SELECT *
FROM Aggregated
WHERE ABS(d1_amount - d2_amount) >= 5
ORDER BY id

逻辑说明 #

这种写法通过纵向合并两张表的原始数据，再按ID分组做条件聚合，自然覆盖了仅存在于单表的ID（另一张表的金额汇总结果为空，通过COALESCE转为0）。对比原方案：

• 避免了FULL OUTER JOIN的关联语法
• 仅在聚合阶段使用两次COALESCE，后续查询逻辑无需重复调用
• 整体代码结构更紧凑，可读性更强

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