Sql Server数据库中isnull、iif、case when三种方式的用法和空值判断

ISNULL、IIF 和 CASE WHEN 是常用的三种方式。以下是它们的详细用法和比较：

1. ISNULL() 函数

基本用法

-- 替换 NULL 值
SELECT ISNULL(column_name, '默认值') AS result
FROM table_name;

-- 实际示例
SELECT 
    ProductName,
    ISNULL(UnitPrice, 0) AS Price,  -- 如果 UnitPrice 为 NULL，则显示 0
    ISNULL(Description, '暂无描述') AS Description
FROM Products;

特点

专门用于处理 NULL 值
只接受两个参数：检查的表达式和替换值
如果第一个参数不是 NULL，返回原值；如果是 NULL，返回第二个参数

2. IIF() 函数

基本用法

-- 语法：IIF(boolean_expression, true_value, false_value)
SELECT IIF(column_name IS NULL, '空值', '非空值') AS result
FROM table_name;

-- 实际示例
SELECT 
    EmployeeName,
    IIF(Salary IS NULL, '未设置', 
        IIF(Salary > 5000, '高薪', '普通')) AS SalaryLevel
FROM Employees;

复杂逻辑示例

-- 多层嵌套（可读性较差）
SELECT 
    IIF(Age IS NULL, '年龄未知',
        IIF(Age < 18, '未成年',
            IIF(Age < 60, '成年人', '老年人'))) AS AgeGroup
FROM Persons;

3. CASE WHEN 表达式

基本用法

-- 简单 CASE 表达式
SELECT 
    CASE column_name
        WHEN NULL THEN '空值'  -- 注意：这种写法无法判断 NULL！
        ELSE '非空值'
    END AS result
FROM table_name;

-- 正确的 NULL 判断（使用搜索式 CASE）
SELECT 
    CASE 
        WHEN column_name IS NULL THEN '空值'
        ELSE '非空值'
    END AS result
FROM table_name;

复杂条件示例

-- 多条件判断
SELECT 
    ProductName,
    CASE 
        WHEN Price IS NULL THEN '价格待定'
        WHEN Price = 0 THEN '免费'
        WHEN Price < 100 THEN '低价'
        WHEN Price BETWEEN 100 AND 500 THEN '中等'
        ELSE '高价'
    END AS PriceCategory,
    CASE 
        WHEN StockQuantity IS NULL THEN 0
        ELSE StockQuantity
    END AS ActualStock
FROM Products;

4. 空值判断的注意事项

重要区别

-- 错误示例：直接使用 = 判断 NULL
SELECT * FROM table WHERE column = NULL;  -- 不会返回任何结果！

-- 正确示例：使用 IS NULL
SELECT * FROM table WHERE column IS NULL;

-- 或使用 ISNULL 函数
SELECT * FROM table WHERE ISNULL(column, '') = '';

COALESCE() 函数（替代方案）

-- 与 ISNULL 类似，但更强大（可接受多个参数）
SELECT 
    COALESCE(column1, column2, column3, '默认值') AS result
FROM table_name;
-- 返回第一个非 NULL 值

5. 三种方式的比较和选择建议

特性	ISNULL	IIF	CASE WHEN
可读性	简单明了	中等	最好
灵活性	最低	中等	最高
参数数量	2个	3个	多个
复杂逻辑	不支持	支持简单嵌套	完美支持
SQL标准	SQL Server 特有	SQL Server 2012+	标准 SQL

选择建议：

简单 NULL 替换 → 使用 ISNULL() 简单条件判断 → 使用 IIF() 复杂逻辑或多条件 → 使用 CASE WHEN 多参数选择第一个非 NULL → 使用 COALESCE()

性能示例对比

-- 示例：处理订单折扣
SELECT 
    OrderID,
    -- 使用 ISNULL
    ISNULL(Discount, 0) AS Discount1,

    -- 使用 IIF
    IIF(Discount IS NULL, 0, Discount) AS Discount2,

    -- 使用 CASE WHEN
    CASE 
        WHEN Discount IS NULL THEN 0
        ELSE Discount
    END AS Discount3,

    -- 使用 COALESCE
    COALESCE(Discount, 0) AS Discount4
FROM Orders;

6. 最佳实践

-- 1. 处理数值型字段
SELECT 
    ProductID,
    ISNULL(StockQuantity, 0) AS Stock,  -- 简洁
    COALESCE(ReorderLevel, MinimumStock, 10) AS ReorderPoint  -- 多备选
FROM Products;

-- 2. 处理字符串字段
SELECT 
    CustomerName,
    CASE 
        WHEN Address IS NULL THEN '地址未提供'
        WHEN Address = '' THEN '地址为空'
        ELSE Address
    END AS ShippingAddress
FROM Customers;

-- 3. 在 WHERE 子句中使用
SELECT * FROM Employees
WHERE ISNULL(DepartmentID, 0) = 0
   OR DepartmentID = @DeptID;

-- 4. 在 ORDER BY 中使用
SELECT * FROM Products
ORDER BY 
    CASE 
        WHEN Price IS NULL THEN 1
        ELSE 0
    END,
    Price ASC;

总结：根据具体场景选择最合适的方案。对于简单的 NULL 替换，ISNULL 最简洁；对于需要兼容其他数据库的情况，使用 CASE WHEN；对于简单的条件判断，IIF 提供更紧凑的语法。