空值在查询中的处理:
在查询的时候,空值的存在,会影响查询数据结果的质量,甚至不准确,
我们要知道:null<>null,null<>0,空值null与其他数据的四则运算的结果都是空值。
以下处理讲述一些在查询中处理空值的函数。
1、在忽略空值的情况下查询奖金为空员工的工资加奖金作为收入:
SQL> select ename,sal,comm,sal+comm as income
2 from emp
3 where comm is null;
ENAME SAL COMM INCOME
---------- ---------- ---------- ----------
SMITH 800
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
10 rows selected.
从查询结果的数据看,income字段为空,则表示对应的员工收入为空,这是明显的错误。
2、以下运用到以下空值处理函数:
1》nvl(expr1,expr2):
当expr1不为空值,返回expr1,当为空值,返回expr2.
SQL> select ename,sal,comm,sal+nvl(comm,0) income
2 from emp
3 where comm is null;
ENAME SAL COMM INCOME
---------- ---------- ---------- ----------
SMITH 800 800
JONES 2975 2975
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
10 rows selected.
2》nvl2(expr1,expr2,expr3):
当expr1不为空值,返回expr3,当为空值,返回expr2。
SQL> select ename,sal,comm,nvl2(comm,sal+comm,sal) income
2 from emp
3 where comm is null;
ENAME SAL COMM INCOME
---------- ---------- ---------- ----------
SMITH 800 800
JONES 2975 2975
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
10 rows selected.
3》nullif(expr1,expr2):
expr1,expr2当两值相等,返回空值,两值不等时,返回expr1。
SQL> select nullif(3,2),nullif(100,100)
2 from dual;
NULLIF(3,2) NULLIF(100,100)
----------- ---------------
3
4》coalesce(expr1,expr2,...exprn):
返回第一个不为空值null的表达式exprn。
查看部门号为30的员工相关信息,显示奖金,当奖金为空值时,并标明为1。
SQL> select ename,sal,comm,coalesce(comm,1),
2 nvl(sal+comm,sal) income,deptno
3 from emp
4 where deptno = 30;
ENAME SAL COMM COALESCE(COMM,1) INCOME DEPTNO
---------- ---------- ---------- ---------------- ---------- ----------
ALLEN 1600 300 300 1900 30
WARD 1250 500 500 1750 30
MARTIN 1250 1400 1400 2650 30
BLAKE 2850 1 2850 30
TURNER 1500 0 0 1500 30
JAMES 950 1 950 30
6 rows selected.
上面的4个函数中,都要求函数式里面的表达式的数据类型要相同。
SQL> select coalesce('','next','su','xing') from dual;
COAL
----
next