如何用CASE语句处理MySQL FULL JOIN后列的动态替换问题

需求说明 #

需要对income和outcome表执行FULL JOIN，当outcome.point或outcome.date为NULL时，用income对应列的值替代，最终返回包含inc（收入）和out（支出）的合并结果，要求必须使用JOIN和CASE语句，可使用UNION。

问题分析 #

你之前使用CASE语句时出现point列全为NULL的情况，大概率是CASE的条件判断逻辑错误——应该优先判断outcome的列是否为NULL，再决定是否取income的对应值，而非反过来。

正确SQL查询语句 #

SELECT
    -- 当outcome的point为空时，使用income的point
    CASE 
        WHEN o.point IS NULL THEN i.point 
        ELSE o.point 
    END AS point,
    -- 当outcome的date为空时，使用income的date
    CASE 
        WHEN o.date IS NULL THEN i.date 
        ELSE o.date 
    END AS date,
    i.inc,
    o.ou_t AS out
FROM income i
FULL JOIN outcome o 
    -- 关联条件：point相等，且日期的日期部分匹配（适配两张表的日期格式差异）
    ON i.point = o.point 
    AND LEFT(i.date, 10) = LEFT(o.date, 10);

语句说明 #

1. FULL JOIN：返回两张表中所有匹配和不匹配的记录，确保没有遗漏任何收入或支出数据；当某张表无对应记录时，对应列会返回NULL。
2. CASE语句：针对outcome的point和date列做NULL判断，若为空则用income的对应值替换，保证最终结果的point和date列无NULL。
3. 关联条件处理：由于两张表的date格式不一致（income为短日期，outcome为带时分秒的长日期），使用LEFT()函数截取日期前10位（YYYY-MM-DD部分）进行匹配，确保同point同日期的记录能正确关联。

可选UNION实现方式（同样满足要求） #

如果更倾向用UNION拆分逻辑，也可以这样写：

-- 取income与outcome匹配的记录 + 仅存在于income的记录
SELECT
    CASE 
        WHEN o.point IS NULL THEN i.point 
        ELSE o.point 
    END AS point,
    CASE 
        WHEN o.date IS NULL THEN i.date 
        ELSE o.date 
    END AS date,
    i.inc,
    o.ou_t AS out
FROM income i
LEFT JOIN outcome o 
    ON i.point = o.point 
    AND LEFT(i.date, 10) = LEFT(o.date, 10)

UNION

-- 取仅存在于outcome的记录
SELECT
    o.point AS point,
    o.date AS date,
    NULL AS inc,
    o.ou_t AS out
FROM outcome o
LEFT JOIN income i 
    ON o.point = i.point 
    AND LEFT(o.date, 10) = LEFT(i.date, 10)
WHERE i.point IS NULL;

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