2.3.4 空值参与运算
1.前言
在MySQL查询中,常常遇到某些元组的字段为NULL,其参与的运算就全都为NULL。如何让字段为NULL的元组能按照我们指定的值参与运算呢?本篇博客将教你IFNULL() 函数的使用。
2.例子
给定一张员工表 employees
,现在的需求是根据表中的 salary
和 commission_pct
(即绩效奖金比例,乘工资即为奖金) ,计算每个员工的年总工资。
从表中可以看到,并不是所有员工都有奖金,没有奖金的员工的 commission_pct
为 (NULL) 。如果都按公式:
a
n
n
u
a
l
S
a
l
a
r
y
=
s
a
l
a
r
y
×
(
1
+
c
o
m
m
i
s
s
i
o
n
P
c
t
)
×
12
annualSalary=salary \times (1+commissionPct)\times 12
annualSalary=salary×(1+commissionPct)×12
计算年总工资的话,那么没有奖金的员工的 annual salary
也为 (NULL) :
SELECT employee_id, last_name, salary, commission_pct, salary * (1+commission_pct) * 12 AS "annual salary"
FROM employees;
查询结果:
可以看到,只要 commission_pct
为 (NULL) 的员工,其 annual salary
也为 (NULL) 。这是非常不合理的,因为 (NULL) 的意思不是”为零“,也不是”没有“;而是”不知道“。退一万步来说,就算不知道员工的 commission_pct
,起码还有基本的 salary × 12
可以发。
这时候,我们就可以引入”空值参与运算“的概念。在MySQL中,用 IFNULL
函数来实现。例如:
IFNULL(commission_pct, 0)
上述 IFNULL
函数表示:如果字段 commission_pct
不是 (NULL) ,就按 commission_pct
本来的值计算;而如果 commission_pct
为 (NULL) ,就按 0
计算。如下代码所示:
SELECT employee_id, last_name, salary, commission_pct, salary * (1 + IFNULL(commission_pct, 0) * 12) AS "annual salary"
FROM employees;
查询结果:
有了 IFNULL
函数,在 commission_pct
为 (NULL) 时,就可以按照我们指定的值进行计算。
【结论】
- 只要在运算中,带有 (NULL) 的元组,在没有其他措施的干预下,其运算结果必为 (NULL) 。
- 运用
IFNULL
函数,在碰到字段为 (NULL) 的元组时能够按照我们指定的值参与运算。从而保证我们计算的结果不会出现 (NULL) 。