SQL COALESCE 函数: 有效处理 NULL 值

摘要: 本教程将向你介绍 SQL COALESCE 函数，并向你展示如何在实际场景中应用此函数。

SQL COALESCE 函数简介

COALESCE 函数接受多个参数，并返回第一个非 NULL 参数。以下说明了 COALESCE 函数的语法。

COALESCE(argument1, argument2,...);Code language: SQL (Structured Query Language) (sql)

COALESCE 函数从左到右计算其参数。它停止计算，直到找到第一个非 NULL 参数。这意味着根本不会计算所有剩余的参数。

如果所有参数都为 NULL，则 COALESCE 函数返回 NULL。

下面的语句返回 1，因为 1 是第一个非 NULL 参数。

SELECT COALESCE(1,2,3); -- return 1Code language: SQL (Structured Query Language) (sql)

下面的语句返回 Not NULL，因为它是不计算为 NULL 的第一个字符串参数。

SELECT COALESCE(NULL,'Not NULL','OK'); -- return Not NULLCode language: SQL (Structured Query Language) (sql)

如果你使用以下语句

SELECT 1/0; -- division by zeroCode language: SQL (Structured Query Language) (sql)

你将得到除零错误。

但是，以下语句返回 1，不会发出任何错误

SELECT COALESCE(1,1/0); -- return 1Code language: SQL (Structured Query Language) (sql)

这是因为 COALESCE 函数短路。在找到第一个非 NULL 参数后，它停止计算剩余的参数。

几乎所有关系数据库系统都支持 COALESCE 函数，例如 MySQL、PostgreSQL、Oracle、Microsoft SQL Server、Sybase。

请注意，COALESCE 函数是 NVL 函数最通用的函数，并且可以代替 NVL 函数使用。

SQL COALESCE 示例

假设我们有一个具有以下结构和数据的 products 表

CREATE TABLE products (
	ID INT PRIMARY KEY,
	product_name VARCHAR(255) NOT NULL,
	product_summary VARCHAR(255),
	product_description VARCHAR(4000) NOT NULL,
	price NUMERIC (11, 2) NOT NULL,
	discount NUMERIC (11, 2),
	CHECK (net_price >= discount)
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO products (
	ID,
	product_name,
	product_summary,
	product_description,
	price,
	discount
)
VALUES
	(
		1,
		'McLaren 675LT',
		'Inspired by the McLaren F1 GTR Longtail',
		'Performance is like strikin and the seven-speed dual-clutch gearbox is twice as fast now.',
		349500,
		1000
	),
	(
		2,
		'Rolls-Royce Wraith Coupe',
		NULL,
		'Inspired by the words of Sir Henry Royce, this Rolls-Royce Wraith Coupe is an imperceptible force',
		304000,
		NULL
	),
	(
		3,
		'2016 Lamborghini Aventador Convertible',
		NULL,
		'Based on V12, this superveloce has been developed as the Lamborghini with the sportiest DNA',
		271000,
		500
	);Code language: SQL (Structured Query Language) (sql)

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

将 SQL COALESCE 用于替换 NULL 值

使用数据库表中的数据时，通常使用 COALESCE 函数为 NULL 值替换默认值。

假设你必须在网页中显示 products 表中所有信息的商品。有些商品可能没有摘要，而另一些商品则有。

在这种情况下，你可以使用 COALESCE 函数来返回商品摘要，如果未提供商品摘要，则从商品描述中获取前 50 个字符。

SELECT
	ID,
	product_name,
	COALESCE (
		product_summary,
		LEFT (product_description, 50)
	) excerpt,
	price,
	discount
FROM
	products;Code language: SQL (Structured Query Language) (sql)

你可以使用 CONCAT 函数在摘录末尾添加 (…)，以便向用户更清楚地表明他们正在阅读的文本只是摘录，并且如果他们单击阅读原文链接，还可以获取更多内容。

SELECT
	ID,
	product_name,
	COALESCE (
		product_summary,
		CONCAT(
			LEFT (product_description, 50),
			'...'
		)
	) excerpt,
	price,
	discount
FROM
	products;Code language: SQL (Structured Query Language) (sql)

在表达式中使用 SQL COALESCE 函数

假设你需要计算所有商品的净价格，并且想出了以下 查询

SELECT 
    id, 
    product_name, 
    (price - discount) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

对于 Rolls-Royce Wraith Coupe，净价格为 NULL。这是因为此商品的折扣为 NULL，当你在计算中使用这个 NULL 值时，结果为 NULL 值。

要解决这个问题，你可以将 discount 列中的所有 NULL 值更新为 0。

UPDATE products 
SET 
    discount = 0
WHERE
    discount IS NULL;Code language: SQL (Structured Query Language) (sql)

或者，你可以按如下方式使用 COALESCE 函数

SELECT 
    id, 
    product_name, 
    price,
    discount,
    (price - COALESCE(discount,0)) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

现在可以正确计算净价格。

SQL COALESCE 和 CASE 表达式

COALESCE 函数是 CASE 表达式的语法。这意味着表达式

COALESCE(argument1,argument2,argument3);Code language: SQL (Structured Query Language) (sql)

可以使用以下 CASE 表达式改写

CASE
  WHEN (argument1 IS NOT NULL) THEN argument1
  WHEN (argument2 IS NOT NULL) THEN argument2
  ELSE argument3
ENDCode language: SQL (Structured Query Language) (sql)

例如，你可以使用 CASE 表达式改写用于根据价格和折扣计算净价的查询，如下所示

SELECT 
    id,
    product_name,
    price,
    discount,
    (price - 
      CASE
        WHEN discount IS NOT NULL THEN discount
        ELSE 0
    END) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

查询返回的结果与使用 COALESCE 函数的结果相同。

在本教程中，你已学习如何使用 SQL COALESCE 函数处理数据库表中的 NULL 值。