SQL Server中CASE值为NULL时如何不返回内容及函数NULL结果处理

这个问题我之前也碰到过，其实核心就是要么从源头避免生成NULL行，要么把已经生成的NULL行过滤掉，给你两种实用的解决思路：

思路1：从源头避免生成NULL行（推荐） #

如果你的查询是通过UNION ALL拼接多个CASE语句来生成多行结果，那完全可以把CASE去掉，直接给每个分类加WHERE条件，只返回符合条件的行。这样就不会产生NULL记录了。

举个例子，假设你原来的代码是这样的：

SELECT CASE WHEN Category = 'Fruits' THEN Item END AS Result
FROM YourTable
UNION ALL
SELECT CASE WHEN Category = 'Vegetables' THEN Item END AS Result
FROM YourTable
UNION ALL
SELECT CASE WHEN Category = 'Meat' THEN Item END AS Result
FROM YourTable
UNION ALL
SELECT CASE WHEN Category = 'Dairy' THEN Item END AS Result
FROM YourTable
UNION ALL
SELECT CASE WHEN Category = 'Grains' THEN Item END AS Result
FROM YourTable

改成下面这样，就只会返回有对应分类数据的行：

SELECT Item AS Result
FROM YourTable
WHERE Category = 'Fruits'
UNION ALL
SELECT Item AS Result
FROM YourTable
WHERE Category = 'Vegetables'
UNION ALL
SELECT Item AS Result
FROM YourTable
WHERE Category = 'Meat'
UNION ALL
SELECT Item AS Result
FROM YourTable
WHERE Category = 'Dairy'
UNION ALL
SELECT Item AS Result
FROM YourTable
WHERE Category = 'Grains'

这种方式更高效，因为数据库只会扫描符合条件的行，不会生成多余的NULL记录。

思路2：过滤已生成的NULL行 #

如果你必须保留CASE表达式（比如有更复杂的逻辑），那可以把整个查询放到子查询里，然后在外层用WHERE Result IS NOT NULL过滤掉NULL行。

示例代码：

SELECT Result
FROM (
    -- 这里是你原来的查询逻辑，会生成包含NULL的结果集
    SELECT CASE WHEN Category = 'Fruits' THEN Item END AS Result
    FROM YourTable
    UNION ALL
    SELECT CASE WHEN Category = 'Vegetables' THEN Item END AS Result
    FROM YourTable
    UNION ALL
    SELECT CASE WHEN Category = 'Meat' THEN Item END AS Result
    FROM YourTable
    UNION ALL
    SELECT CASE WHEN Category = 'Dairy' THEN Item END AS Result
    FROM YourTable
    UNION ALL
    SELECT CASE WHEN Category = 'Grains' THEN Item END AS Result
    FROM YourTable
) AS SubQuery
WHERE Result IS NOT NULL

这样不管子查询里有没有生成NULL行，最后都会被过滤掉，返回的结果就只有非空的记录了。

补充说明 #

如果你的函数是通过其他方式生成多行结果（比如递归CTE、表值函数的内部逻辑），核心思路还是一样的：在生成结果的最后一步，加上WHERE 列名 IS NOT NULL的条件，把NULL行排除掉。

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