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