数据库中的空值(NULL)经常会导致一些不可预知的错误,我们需要使用数据库提供的方法对空值进行处理,确保数据查询的准确性。

如下表所示,红色标记的值为空值。

分别使用5种方法对数据库的空值进行判断和处理:

1.使用COALESCE函数

COALESCE(expr1,expr2,expr3,…)函数接受一个输入参数的列表,返回第1个非空的参数。如果所有的参数都为空,则返回空值。

案例:

假如我们现在需要查找员工的联系电话,查找的规则如下:先找移动电话;如果没有移动电话,就找工作电话;如果没有工作电话,就找家庭电话;如果没有家庭电话,就找紧急联系人电话;如果以上电话都没有找到,则返回“N/A”。

实现SQL:

 SELECT t.emp_id,
 COALESCE(t.mobile_phone,t.work_phone,t.home_phone,t.emergency_phone,'N/A') AS phone
 FROM emp_contact t;

执行结果:

2.使用NULLIF函数

NULLIF(expr1,expr2)函数接受两个输入参数,如果第1个参数等于第2个参数,返回空值;否则,返回第1个参数的值。

实例SQL:

 SELECT NULLIF(1,2),NULLIF(2,2)
 FROM DUAL;

执行结果:

1和2不相等,故返回第一个参数值1。

2和2相等,故返回空。

NULLIF函数的一个常见用途是防止除零错误,例如: 

通过NULLIF函数处理后就不会报错了:

3.使用CASE表达式

案例:

查询部门ID为2的部门员工全年收入(包括工资和奖金)

实现SQL:

SELECT t.emp_name,
  t.salary AS "工资",
  t.bonus AS "奖金",
  CASE WHEN t.bonus IS NULL THEN t.salary*12 
       ELSE  t.salary*12 + t.bonus 
  END AS "全年收入"    
FROM employee t WHERE t.dept_id=2;

执行结果:

4.使用NVL(expr1,expr2)函数

NVL(expr1,expr2)函数返回第1个非空的参数值,等价于只有两个参数的COALESCE函数。

案例:

查询部门ID为2的部门员工全年收入(包括工资和奖金)

在不使用NVL函数对空值进行处理的情况下,会查询出错误的结果:

使用NVL函数对奖金的值进行处理

实现SQL:

SELECT t.emp_name,
t.salary AS "工资",t.bonus AS "奖金",
t.salary*12 + NVL(t.bonus,0) AS "全年收入"
FROM employee t WHERE t.dept_id=2;

执行结果:

这个结果才是我们正确的,即使奖金为空,也不影响全年收入的出值。

5.使用NVL2(expr1,expr2,expr3)函数

NVL2(expr1,expr2,expr3)函数包含3个参数,如果第1个参数不为空,返回第2个参数的值;否则,返回第3个参数的值。

案例:

查询部门ID为2的部门员工全年收入(包括工资和奖金)

实现SQL:

SELECT t.emp_name,
t.salary AS "工资",t.bonus AS "奖金",
NVL2(t.bonus,t.salary*12 + t.bonus,t.salary*12 ) AS "全年收入"
FROM employee t WHERE t.dept_id=2;

执行结果:

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐