Skip to main content
 首页 » 数据库

PostgreSQL COALESCE 和 NULLIF 函数

2022年07月19日335zhoujg

本文学习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
阅读延展