PostgreSQL COALESCE 和 NULLIF 函数

曹和正

2023-12-01

本文学习PostgreSQL COALESCE 和 NULLIF函数。COALESCE它返回第一个非空参数，并通过示例让你了解如何在select语句中有效处理空值。NULLIF需要两个参数，参数相同返回null，否则返回第一个参数。通过同时学习两个函数，可以区分两者，同时可以组合使用，增强你SQL的健壮性。

PostgreSQL COALESCE 函数语法

COALESCE 函数语法:

COALESCE (argument_1, argument_2, …);

参数可以有无限个，总是返回第一个非空参数，如果所有参数都是null，则返回null。

COALESCE 函数从左到右开始评估每个参数，直到发现第一个非空参数，所有剩余参数被忽略不会被评估。标准SQL中对应的函数为 NVL 和 IFNULL ，mysql 为 ifnull 函数， oracle 为nvl 函数。

请看示例：

SELECT COALESCE(1, 2);        -- return 1
SELECT COALESCE(NULL, 2 , 1); -- return 2

通常在查询语句中使用 COALESCE函数使用缺省值代替null值。假设我们要展示blog的摘要信息，如果没有摘要则去内容的前150字符作为摘要，使用COALESCE函数实现：

SELECT
	COALESCE (excerpt, LEFT(CONTENT, 150)) excerpt
FROM
	posts;

PostgreSQL COALESCE 示例

下面通过示例展示 COALESCE 函数的用法，首先创建表：

CREATE TABLE items (
	ID serial PRIMARY KEY,            -- 主键 
	product VARCHAR (100) NOT NULL,   -- 产品名称
	price NUMERIC NOT NULL,           -- 产品价格
	discount NUMERIC                  -- 产品折扣
);

插入测试数据：

INSERT INTO items (product, price, discount)
VALUES
	('A', 1000 ,10),
	('B', 1500 ,20),
	('C', 800 ,5),
	('D', 500, NULL);

现在需要查询产品的净价（实际价格），使用下面公示：

-- net_price = price - discount;
SELECT product, (price - discount) AS net_price
FROM items;

返回结果：

product	net_price
A	990
B	1480
C	795
D

我们注意到最后一行产品D的净价为0。问题是因为折扣字段值为null 造成的，因为PostgreSQL计算遇到null值会返回null。为了获得正确结果，我们需要假设折扣为空，即没有折扣或为0。我们使用coalesce 函数实现：

SELECT product, (price - COALESCE(discount,0)) AS net_price
FROM items;

返回结果：

product	net_price
A	990
B	1480
C	795
D	500

现在D的净价为500，因为计算时使用0代替折扣null值。除了使用 coalesce 函数，我们也可以使用 case 表达式处理null值。请看示例：

SELECT product,
        (
            price - CASE
                        WHEN discount IS NULL THEN 0
                        ELSE discount
                     END
        ) AS net_price
FROM items;

返回结果一致。从性能上看两者相同。当推荐使用 COALESCE 函数，它比 CASE 表达式更简洁易读。

NULLIF 函数语法

NULLIF 函数是PostgreSQL提供的最常用的条件表达式之一，语法如下：

NULLIF(argument_1,argument_2);

如果两个参数相等返回null，否则返回第一个参数。请看示例：

SELECT NULLIF (1, 1);     -- return NULL

SELECT NULLIF (1, 0);     -- return 1

SELECT NULLIF ('A', 'B'); -- return A

NULLIF 函数示例

下面通过示例学习 nullif函数，首先创建表：

CREATE TABLE posts (
    id serial primary key,
	title VARCHAR (255) NOT NULL,
	excerpt VARCHAR (150),
	body TEXT,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP
);

插入几条测试数据：

INSERT INTO posts (title, excerpt, body)
VALUES
      ('test post 1','test post excerpt 1','test post body 1'),
      ('test post 2','','test post body 2'),
      ('test post 3', null ,'test post body 3');

我们的需求是显示博客列表页面，显示标题和摘要信息。当记录没有摘要时，我们取内容的前40个字符作为摘要。下面简单使用语句实现：

SELECT ID, title, excerpt
FROM posts;

返回结果：

id	title	excerpt
1	test post 1	test post excerpt 1
2	test post 2
3	test post 3

excerpt 字段的值：id为2 是空串，id为3是为 null 。因为有null值，我们使用上面提到的coalesce函数实现：

SELECT id, title, COALESCE (excerpt, LEFT(body, 40)) excerpt
FROM posts;

返回结果：

id	title	excerpt
1	test post 1	test post excerpt 1
2	test post 2
3	test post 3	test post body 3

因为同时有null值和空串，我们混合使用 nullif函数：

SELECT id, title, COALESCE (
                    NULLIF (excerpt, ''),
                    LEFT (body, 40)
				)
FROM posts;

首先，如果excerpt是空串则nullif 函数返回null，否则它返回 excerpt 。nullif函数返回结果给coalesce使用。

其次，coalesce 函数检查第一个参数，它有nullif函数提供的，如果为null则返回博客内容前40字符，否则返回 excerpt本身（此时excerpt不为null）。

返回结果：

id	title	coalesce
1	test post 1	test post excerpt 1
2	test post 2	test post body 2
3	test post 3	test post body 3

使用nullif函数防止除数为零

首先创建表：

CREATE TABLE members (
	ID serial PRIMARY KEY,
	first_name VARCHAR (50) NOT NULL,
	last_name VARCHAR (50) NOT NULL,
	gender SMALLINT NOT NULL -- 1: male, 2 female
);

插入示例数据：

INSERT INTO members ( first_name, last_name, gender)
            VALUES
                ('John', 'Doe', 1),
                ('David', 'Dave', 1),
                ('Bush', 'Lily', 2);

我们希望计算男女成员的比例：

SELECT
	(SUM (
		CASE
            WHEN gender = 1 THEN 1
            ELSE 0
		END
	) / SUM (
		CASE
            WHEN gender = 2 THEN 1
            ELSE 0
		END
	) ) * 100 AS "Male/Female ratio"
FROM
	members;

因为 gender = 1 有两条， 2 有1条，返回值为 200% ,结果正确。现在删除female记录：

DELETE FROM members
WHERE gender = 2;

再次执行上面查询返回错误，因为除数为零。让我们使用nullif函数进行修正：

SELECT
	(
		SUM (
			CASE
                WHEN gender = 1 THEN 1
                ELSE 0
			END
		) / NULLIF (
			SUM (
				CASE
                    WHEN gender = 2 THEN 1
                    ELSE 0
				END
			), 0
		)
	) * 100 AS "Male/Female ratio"
FROM members;

使用nullif函数检查sum函数值是否为0，如果为 0 则返回null ，从而整个结果为null ，成功避免了除数为 0 .

总结

本文介绍了COALESCE 和 NULLIF 函数，通过对比学习，可以在合适的场景中组合使用增强你的SQL能力。

PostgreSQL COALESCE 和 NULLIF 函数

PostgreSQL COALESCE 函数语法

PostgreSQL COALESCE 示例

NULLIF 函数语法

NULLIF 函数示例

使用nullif函数防止除数为零

总结

相关阅读

相关文章

相关问答

相关文档