在CASE语句中使用SELECT子查询并处理NULL值的问题

我有两张SQL表：一张存储库存信息，另一张样式表记录库存的使用方式。部分作业的信息包含在打印文件中，此时库存表的Logic值为BIN；否则会存储模板名称，需查询样式表获取库存使用方式。库存可应用于10个位置，但模板表不会记录库存无法使用的场景（例如单面作业的背面）。

我编写了一个存储过程，可根据Logic值是否为BIN返回对应详情，但样式表中无匹配记录时会返回NULL，我需要屏蔽该NULL值。但仅在子查询的列名外使用ISNULL函数无效果，希望避免嵌套CASE语句来检查NULL，想排查问题所在。

相关SQL代码如下：

WITH STYLES AS 
(
    SELECT 
        @JOBNAME AS JobName, Logic AS StyleName 
    FROM 
        [dbo].[CON_Tbl_511_DigitalStocks]
    WHERE  
        JOBNAME = @JOBNAME
)
SELECT TOP 1 
    [Logic], 
    [S1F], [S1B], [S2F], [S2B], 
    [S3F], [S3B], [S4F], [S4B],
    [S5F], [S5B],
    CASE 
        WHEN b.stylename = 'BIN' 
            THEN --checks if there is a style for this job
                CASE 
                    WHEN S1F = '' THEN '' 
                    ELSE '1' 
                END -- if a stock code is specified then return the bin name ("1")
            ELSE (SELECT PAGE 
                  FROM [dbo].[CON_Tbl_512_DigitalLogic] 
                  WHERE STYLENAME = B.StyleName 
                    AND STOCKREF = 'S1F') 
    END AS S1F_LOGIC,    -- If a style is used return the style instruction for this bin and side 
    CASE 
        WHEN b.stylename = 'BIN' -- repeat this for all bins/sides
            THEN 
                CASE WHEN S1B = '' THEN ''
                ELSE '1' 
            END 
            ELSE (SELECT PAGE 
                  FROM [dbo].[CON_Tbl_512_DigitalLogic] 
                  WHERE STYLENAME = B.StyleName AND STOCKREF = 'S1B') 
    END AS S1B_LOGIC,
    CASE WHEN b.stylename = 'BIN' THEN 
            CASE WHEN S2F = '' THEN ''
                ELSE '2' END 
            ELSE  (SELECT PAGE FROM [dbo].[CON_Tbl_512_DigitalLogic] WHERE STYLENAME = B.StyleName AND STOCKREF = 'S2F') END AS S2F_LOGIC -- this one returns NULL as there is no instruction required for "2SF"
FROM 
    [CON_Tbl_511_DigitalStocks] A 
JOIN 
    STYLES B ON A.JOBNAME = B.JOBNAME
WHERE 
    A.JobName = @JobName 

当前代码运行正常，但S2F_LOGIC因stockref列无'S2F'值返回NULL，使用SELECT ISNULL(PAGE, '')仍无法解决。

问题根源 #

你遇到的核心问题是：当子查询没有匹配到任何行时，它返回的是NULL（整个子查询的结果），而非某一行的PAGE字段为NULL。此时你在子查询内部写ISNULL(PAGE, '')完全无效——因为子查询根本没返回行，这个函数根本不会执行。

修复方法 #

把ISNULL包裹整个子查询，而不是只包裹PAGE字段。以S2F_LOGIC为例，修改后的CASE分支如下：

CASE WHEN b.stylename = 'BIN' THEN 
        CASE WHEN S2F = '' THEN ''
            ELSE '2' END 
        ELSE ISNULL((SELECT PAGE FROM [dbo].[CON_Tbl_512_DigitalLogic] WHERE STYLENAME = B.StyleName AND STOCKREF = 'S2F'), '') 
END AS S2F_LOGIC

这样当子查询无匹配行返回NULL时，ISNULL会将其替换为空字符串，达到屏蔽NULL的效果。其他类似的*_LOGIC字段都可以按这个方式修改。

代码优化建议（可选） #

如果所有位置的逻辑都重复，建议用左连接+条件聚合替代重复子查询，让代码更简洁易维护：

WITH STYLES AS 
(
    SELECT 
        @JOBNAME AS JobName, Logic AS StyleName 
    FROM 
        [dbo].[CON_Tbl_511_DigitalStocks]
    WHERE  
        JOBNAME = @JOBNAME
),
LOGIC_DETAILS AS (
    SELECT 
        STYLENAME,
        -- 按STOCKREF分组提取对应PAGE值，无匹配则为空
        MAX(CASE WHEN STOCKREF = 'S1F' THEN PAGE ELSE '' END) AS S1F_PAGE,
        MAX(CASE WHEN STOCKREF = 'S1B' THEN PAGE ELSE '' END) AS S1B_PAGE,
        MAX(CASE WHEN STOCKREF = 'S2F' THEN PAGE ELSE '' END) AS S2F_PAGE
        -- 其他位置（S2B、S3F等）同理补充
    FROM [dbo].[CON_Tbl_512_DigitalLogic]
    WHERE STYLENAME IN (SELECT StyleName FROM STYLES)
    GROUP BY STYLENAME
)
SELECT TOP 1 
    A.[Logic], 
    A.[S1F], A.[S1B], A.[S2F], A.[S2B], 
    A.[S3F], A.[S3B], A.[S4F], A.[S4B],
    A.[S5F], A.[S5B],
    -- 直接引用提前聚合好的结果，避免重复子查询
    CASE 
        WHEN b.stylename = 'BIN' THEN CASE WHEN A.S1F = '' THEN '' ELSE '1' END
        ELSE ISNULL(L.S1F_PAGE, '') 
    END AS S1F_LOGIC,
    CASE 
        WHEN b.stylename = 'BIN' THEN CASE WHEN A.S1B = '' THEN '' ELSE '1' END
        ELSE ISNULL(L.S1B_PAGE, '') 
    END AS S1B_LOGIC,
    CASE 
        WHEN b.stylename = 'BIN' THEN CASE WHEN A.S2F = '' THEN '' ELSE '2' END
        ELSE ISNULL(L.S2F_PAGE, '') 
    END AS S2F_LOGIC
FROM 
    [CON_Tbl_511_DigitalStocks] A 
JOIN 
    STYLES B ON A.JOBNAME = B.JOBNAME
LEFT JOIN
    LOGIC_DETAILS L ON B.StyleName = L.STYLENAME
WHERE 
    A.JobName = @JobName 

这种写法不仅避免了重复的子查询，还让逻辑更清晰，后续扩展其他位置的字段也更方便。

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