了解 SQL 中的 COALESCE () 函数

0 Shares

掌握SQL中的COALESCE函数：全面指南

随着科技的飞速发展，作为一名开发者，掌握最新的技术趋势至关重要。无论您是刚入门的新手还是经验丰富的专家，深入理解字符串操作都能帮助您有效地预处理数据，例如生成不同格式的数据以满足业务需求，并使用SQL Server内置函数进行管理。

除了数据操作外，您还可以通过检查数据集、评估数据值、以及对其进行编码或解码来挖掘数据的更深层含义。这有助于您识别数据集中缺失的值，理解这些缺失值对计算的影响，并简化整体数据处理流程，从而避免因空值导致的潜在错误。

本指南将深入探讨SQL中的COALESCE函数，它在构建复杂程序时扮演着关键角色。本文假设您已具备一定的SQL使用经验，并希望进一步加强对该函数的理解。如果您是SQL新手，我们的SQL指南系列可以帮助您快速入门。

SQL中COALESCE()的作用和用途

SQL中的COALESCE函数，会按照您指定的顺序（如列表）评估每个参数，并返回第一个非空值。简而言之，它会按顺序检查列表中的每个值，并在遇到第一个非空值时立即返回。如果列表中的所有参数均为NULL，则函数将返回NULL。

COALESCE函数不仅限于SQL Server，它也被广泛支持于其他数据库系统，如MySQL、Azure SQL数据库、Oracle和PostgreSQL等。

您可以在以下场景中有效利用COALESCE函数：

处理NULL值。
将多个查询合并为一个查询执行。
避免编写冗长复杂的CASE语句。

当使用COALESCE替代CASE语句（或ISNULL函数）时，它可以接受多个参数，而CASE通常只需要两个。这使得您可以编写更简洁的代码，简化开发流程。

其语法结构如下：

COALESCE(valueOne, valueTwo, valueThree, …, valueX);

SQL Server中的COALESCE函数具有多个特性，包括参数数据类型一致、接受多个参数，以及通过Yield函数级联返回整数类型参数的特性。

延伸阅读：SQL终极备忘单，请收藏以备后用

在深入探讨COALESCE的使用方法之前，让我们先了解一下NULL值。

SQL中的NULL值是什么？

在SQL中，NULL是一个特殊的标记，用于表示数据库中某个值不存在。您可以将其理解为未定义或未知的值。不要将NULL误认为是空字符串或零值，它代表的是缺失的值。表列中出现NULL值通常表示缺少信息。

在实际应用中，如果客户未提供其ID，则电子商务网站数据库中对应的数据列可能会填充NULL值。 SQL中的NULL是一种特殊的状态，它不同于其他编程语言中“不指向特定对象”的概念，它仅仅表示值的缺失。

SQL中的NULL值对关系数据库有着重要影响。首先，它们允许您在使用某些聚合函数时排除特定值。例如，在生产环境中，您可能需要生成总订单列表，其中一些订单可能仍在处理中。使用NULL作为占位符，可以使SUM等函数正确计算总额。

此外，在需要使用AVG函数计算平均值时，如果使用零值，结果会产生偏差。相反，数据库可以使用NULL来忽略这些字段，从而获得更准确的结果。

NULL值并非没有缺点。它们被认为是可变长度的值，可能占用一个或多个字节。如果这些字节数超过数据库中存储的字节数，数据库将为这些字节保留额外的空间，因此与常规值相比，数据库可能会占用更多的硬盘空间。

此外，在使用某些函数时，您可能需要自定义这些函数以处理NULL值，这可能会使您的SQL语句变得更加复杂。

使用COALESCE()处理NULL值

NULL值表示该处应该有一个值，但您不知道这个值应该是什么。在您收集到可以用实际值填充字段的数据之前，NULL值可以被视为占位符。

虽然您可以在数据库中使用NULL值处理多种数据类型（包括小数、字符串、二进制大对象和整数），但在处理数值数据时，最好尽量避免使用NULL值。

当使用NULL值进行数值计算时，您可能需要在代码中添加额外的处理逻辑，这可能会使代码变得复杂。这将在稍后详细讨论。

COALESCE() 可以用于多种方式处理NULL值：

使用COALESCE()将NULL值替换为特定值

您可以使用COALESCE() 为所有NULL值返回一个特定的值。例如，您可能有一个名为“员工”的表，其中包含“薪水”列，如果员工的薪水尚未录入，则该列可能包含NULL值。在进行一些计算时，您可能希望将所有NULL条目视为特定的值（例如0）。以下是具体操作方法。

SELECT COALESCE(salary, 0) AS adjusted_salary
FROM employees;

使用COALESCE()从多个选项中选择第一个非NULL值

有时，您可能需要从一系列表达式中选择第一个非NULL值。这种情况下，通常存在多个包含相关数据的列，您需要根据优先级选择其中的非NULL值。语法仍然保持不变。

COALESCE (expression1, expression2, …)

在实际场景中，假设您有一个联系人表，其中包含preferred_name和full_name两列。您希望生成一个联系人列表，并显示他们的首选名称（如果存在），否则显示全名。以下是如何解决这个问题。

SELECT COALESCE(preferred_name, full_name) AS display_name
FROM contacts.

如果preferred_name不为NULL，则返回该名称。否则，将返回full_name作为显示名称。

使用SQL COALESCE进行字符串连接

在连接字符串时，如果涉及NULL值，您可能会遇到SQL问题。在这种情况下，NULL将作为连接结果返回，但这通常不是我们想要的结果。您可以使用COALESCE函数来解决这个问题。以下是一个示例。

一个简单的字符串连接通常是这样实现的：

SELECT ‘你好，你在哪里，’ || ‘约翰’ || ‘?’ AS example

代码会返回：

示例：你好，你在哪里，约翰？

但是，如果连接中包含NULL值，如下所示：

SELECT ‘你好，你在哪里，’ || null || ‘?’ AS example

输出结果将是NULL。

因为任何与NULL值进行连接的文本字符串都会返回NULL，所以上述结果也是NULL。然而，这个问题可以通过使用COALESCE()来解决。通过使用此函数，您可以返回一个空字符串（或空格）来代替NULL。假设您要列出汽车名称及其制造商，下面是您的查询示例。

SELECT 
car || ‘, 制造商：’ || COALESCE(manufacturer, ‘—') AS car_brand
FROM stock

如果制造商的值为NULL，您将看到“—”而不是NULL。以下是预期结果。

car_brand: outlander，制造商：—;飞驰，制造商：宾利;皇家运动员，制造商：—;皇家轿车，制造商：皇冠

正如您所看到的，NULL结果已经被消除，并且您可以选择插入一个替代的字符串值。

SQL COALESCE函数与数据透视

SQL数据透视是一种将行转换为列的技术。它允许您将数据从“规范化”形式（行数较多，列数较少）转换为“非规范化”形式（行数较少，列数较多）。 COALESCE函数可以与SQL数据透视结合使用，以处理数据透视结果中的NULL值。

在SQL中执行数据透视时，您将行转换为列；结果列是某些数据的聚合函数。如果特定单元格的聚合结果为NULL，可以使用COALESCE将NULL值替换为默认值或有意义的表示形式。以下是一个示例。

假设您有一个销售表，其中包含年份、季度和收入列，您希望对数据进行透视，将年份作为列，并显示每个季度的收入总和。但是，某些季度可能没有收入数据，导致数据透视结果中出现NULL值。在这种情况下，您可以使用COALESCE将透视结果中的NULL值替换为零（0）。

SELECT
    year,
    COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;

标量用户定义函数和SQL COALESCE函数

您可以使用标量用户定义函数（UDF）和COALESCE函数来处理包含NULL值的复杂逻辑。将这两个功能结合使用可以帮助您在SQL查询中实现更复杂的数据转换和计算。考虑一个名为“Employees”的表，其结构如下：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Bonus INT
);

您可能希望计算每个员工的总收入（工资加奖金）。但是，一些员工可能缺少奖金数据。在这种情况下，您可以使用标量UDF来处理工资和奖金的相加，并使用COALESCE来处理NULL值。以下是一个计算总收入的标量UDF示例。

CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @totalEarnings INT;
    SET @totalEarnings = @salary + COALESCE(@bonus, 0);
    RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
       Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;

使用SQL COALESCE进行数据验证

在使用数据库时，您可能需要验证数值数据。例如，假设您有一个名为“products”的表，其中包含产品名称、价格和折扣列。您希望检索每个商品的产品名称、价格和折扣，并将所有NULL折扣值视为0。COALESCE函数可以帮助您实现这一目标。以下是如何使用它：

SELECT product_name, price, COALESCE(discount, 0) AS discount 
FROM products

SQL COALESCE与计算列

计算列是根据表达式或表中其他列计算得出的虚拟列。由于计算列不实际存储在数据库中，因此在处理复杂的场景和数据转换时，您可以通过COALESCE函数来利用它们。以下是一个实际的应用场景示例。

假设您有一个名为“products”的表，其中包含“price”、“discount”和“tax_rate”列。您希望创建一个名为“total_price”的计算列来表示应用折扣和税费后的最终产品价格。如果折扣或税费的值为NULL，则应将其视为0进行计算。以下是如何使用COALESCE来适应这种情况。

CREATE TABLE products(
price DECIMAL(10, 2),
discount DECIMAL(10, 2),
tax_rate DECIMAL(5, 2),
total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1)
);

在上面的代码中，发生了以下情况：

total_price 计算列定义为：(COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1)。
如果价格为 NULL，COALESCE(price, 0) 将确保其被视为0。
如果折扣为 NULL，COALESCE(price*discount, 0) 将确保其被视为0，并且乘法运算不会影响计算。
如果税率为 NULL，COALESCE(1 +tax_rate, 1) 将确保其被视为1，这意味着不征税，并且乘法运算不会影响计算。

通过上述设置，您可以生成一个计算列“total_price”，该列包含实际的最终价格，即使数据中存在缺失的值或NULL值。

SQL COALESCE和CASE表达式

您可以在语法上使用CASE表达式实现与COALESCE函数类似的功能。以下是一个示例：

SELECT
Productname + ‘ ’+ deliverydate productdetails,
dealer,
CASE
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone
ELSE ‘NA’
END
EmergencyContactNumber
FROM
dbo.tb_EmergencyContact

在上面的示例中，CASE查询的作用类似于COALESCE函数。

此外，您还可以在同一个查询中使用COALESCE和CASE表达式。这两种技术可以同时处理NULL值和应用条件逻辑。让我们通过一个示例来说明这一点。

假设您有一个表，其中包含产品ID、产品名称、价格和折扣列。某些产品有特定的折扣，而其他产品则没有。如果产品有折扣，则应显示折扣后的价格，否则应显示正常价格。

SELECT 
    product_id,
    product_name,
    price,
    COALESCE(
        CASE
            WHEN discount > 0 THEN price - (price * discount / 100)
            ELSE NULL
        END,
        price
    ) AS discounted_price
FROM products;

在上面的代码中，“CASE”表达式检查“discount”是否大于零，并计算折扣价格，否则返回NULL。“COALESCE”函数将“CASE”表达式的结果和“price”作为参数。它返回第一个非NULL值，有效地返回折扣价格（如果存在）或正常价格（如果不存在）。

总结

本文介绍了在数据库查询中使用COALESCE函数的各种方法。通过按照指定的顺序评估参数并返回第一个非NULL值，COALESCE函数简化了查询，使其变得高效。

无论是处理NULL值、字符串连接、数据透视、数据验证还是使用计算列，COALESCE都是一个多功能函数。通过掌握COALESCE函数，开发人员可以更好地处理缺失的数据，并创建更加健壮的数据库设计。请记住，技术的掌握需要不断的实践。

您现在可以进一步学习如何在SQL中创建外键约束。