Skip to main content
 首页 » 数据库

PostgreSQL COALESCE 和 NULLIF 函数

2022年07月19日363zhoujg

本文学习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能力。


本文参考链接：https://blog.csdn.net/neweastsun/article/details/119120078
  文章来源：
  https://www.itguest.com/post/acbh3c1.html
阅读延展