SQL查询无结果时返回0而非空值的实现方法求助

问题原因 #

你用的ISNULL(body, 0)只能处理单行列值为NULL的情况，要是整个查询因为WHERE条件过滤后没有匹配行，这个函数根本不会执行，自然出不来0的结果。

两种可行解决方案 #

方案1：用聚合函数兜底（简单高效）

用MAX()这类聚合函数包裹查询结果，聚合函数在无匹配行时会返回NULL，这时再用ISNULL或COALESCE转成0：

SELECT ISNULL(MAX(body), 0) AS body
FROM [EMAIL_MATRIX] 
WHERE body NOT IN (
    SELECT c.Body
    FROM [dbo].[EMAIL_DETAILS] a
    INNER JOIN [Email_matrix] c 
        ON SUBSTRING(a.Subject, 5, LEN(a.Subject) - 4) = c.Subject
        AND a.Body LIKE CONCAT('%', c.body, '%') 
    WHERE CAST(a.Send AS DATE) = '2022-10-04'
)

注意：如果body是字符串类型，建议把0转成字符串，比如ISNULL(MAX(body), '0')，避免类型不匹配报错。

方案2：用UNION ALL手动兜底

先执行原查询，若没有结果则追加返回0的行：

SELECT body
FROM [EMAIL_MATRIX] 
WHERE body NOT IN (
    SELECT c.Body
    FROM [dbo].[EMAIL_DETAILS] a
    INNER JOIN [Email_matrix] c 
        ON SUBSTRING(a.Subject, 5, LEN(a.Subject) - 4) = c.Subject
        AND a.Body LIKE CONCAT('%', c.body, '%') 
    WHERE CAST(a.Send AS DATE) = '2022-10-04'
)
UNION ALL
SELECT 0
WHERE NOT EXISTS (
    SELECT 1
    FROM [EMAIL_MATRIX] 
    WHERE body NOT IN (
        SELECT c.Body
        FROM [dbo].[EMAIL_DETAILS] a
        INNER JOIN [Email_matrix] c 
            ON SUBSTRING(a.Subject, 5, LEN(a.Subject) - 4) = c.Subject
            AND a.Body LIKE CONCAT('%', c.body, '%') 
        WHERE CAST(a.Send AS DATE) = '2022-10-04'
    )
)

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