Left Join匹配失败行的Null值处理：主类别级描述填充

需求说明 #

将表a与表b通过id、main_cat、sub_cat三列做Left Join，优先取表b的description字段；当该组合在表b中不存在导致description为Null时，改用相同id和main_cat匹配到的表b中的main_description作为description的值。

示例数据 #

表a数据

id   main_cat    sub_cat
------------------------
 1      A           X
 2      B           Y
 3      A           Z
 4      C           W

表b数据

id   main_cat    sub_cat    description        main_description
-------------------------------------------------------------------
 1      A           X       Description 1      Main Description A
 2      B           Y       Description 2      Main Description B
 4      C           W       Description 3      Main Description C

期望结果

id  main_cat    sub_cat    description
---------------------------------------------
1      A           X       Description 1
2      B           Y       Description 2
3      A           Z       Main Description A
4      C           W       Description 3

尝试的错误SQL #

SELECT 
    A.ID, A.Main_cat, A.Sub_cat,
    (CASE 
         WHEN B.Description ISNULL 
             THEN B.MainDescription 
             ELSE B.Description 
     END) AS Description 
FROM  
    (SELECT * FROM tblA) AS A  
LEFT JOIN
    tblB AS B ON B.somevalue = 'somevalue' 
              AND B.Main_cat = A.Main_cat  
              AND B.Sub_cat = A.Sub_cat 
              AND A.ID = B.ID

问题分析与正确解决方案 #

错误原因

原SQL仅通过id+main_cat+sub_cat做一次Left Join，当表a中该行在表b无匹配时，整个B表的关联行都是Null，此时B.MainDescription也为Null，CASE WHEN无法取到有效默认值。

方案一：两次Left Join + COALESCE

SELECT
    a.id,
    a.main_cat,
    a.sub_cat,
    COALESCE(b1.description, b2.main_description) AS description
FROM tblA a
-- 第一次关联：匹配完整的id+main_cat+sub_cat
LEFT JOIN tblB b1 
    ON a.id = b1.id 
    AND a.main_cat = b1.main_cat 
    AND a.sub_cat = b1.sub_cat
-- 第二次关联：仅匹配id+main_cat，获取默认的main_description
LEFT JOIN (SELECT DISTINCT id, main_cat, main_description FROM tblB) b2 
    ON a.id = b2.id 
    AND a.main_cat = b2.main_cat

方案二：子查询获取默认值 + COALESCE

SELECT
    a.id,
    a.main_cat,
    a.sub_cat,
    COALESCE(b.description, (
        SELECT TOP 1 main_description 
        FROM tblB 
        WHERE id = a.id AND main_cat = a.main_cat
    )) AS description
FROM tblA a
LEFT JOIN tblB b 
    ON a.id = b.id 
    AND a.main_cat = b.main_cat 
    AND a.sub_cat = b.sub_cat

关键说明 #

• COALESCE函数会返回第一个非Null的值，实现"优先取完整匹配的description，无则取默认main_description"的逻辑。
• 方案一中的DISTINCT用于避免同一个id+main_cat在表b有多条记录时，导致结果行数膨胀；若数据保证唯一，可去掉。
• 方案二中的TOP 1是SQL Server语法，MySQL需替换为LIMIT 1，Oracle需替换为WHERE ROWNUM = 1。

内容的提问来源于stack exchange，提问作者Mohammad Raziuddin Chowdhury