Left join无匹配行时coalesce失效，如何返回指定值1？

核心问题是你把coalesce()用在了JOIN的ON关联条件中，它只负责筛选匹配的行，无法处理左连接后无匹配行时的输出值。要实现无匹配行时输出1，需要在SELECT语句里对t2.id2做coalesce()处理。

修改后的SQL示例 #

SELECT 
    t1.id, 
    t1.id2, 
    COALESCE(t2.id2, 1) AS t2_id2_output
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id
    AND COALESCE(t1.id2, 1) = COALESCE(t2.id2, 1)

原理说明 #

左连接的特性是：当table2没有匹配的记录时，t2的所有字段都会返回NULL。这时候在SELECT中使用COALESCE(t2.id2, 1)，就能把NULL值替换成1，完全符合你的需求。

之前的写法中，ON条件里的coalesce()只是用来判断两行是否满足匹配规则，和最终输出无关。

内容的提问来源于stack exchange，提问作者Jim E Russel