null值处理函数

null值

是一个很特别的值,既不是0也不是空格.它的值是没有定义,不确定的未知值

比如一个案件的追踪表,警方在对犯罪分子一无所知,但在犯罪分子性别一栏不是男就是女,只是此时还不确定

就可以把性别栏设置为未知,当案件侦破到一定程度,警方知道了犯罪分子的性别,既从未知变成了已知.也就是由NULL变成男或女

idle> select ename,job,sal,comm from emp where ename in ('SMITH','WARD');

ENAME    JOB    SAL       COMM

---------- --------- ---------- ----------

SMITH    CLERK    800

WARD    SALESMAN   1250        500

idle> 

如上查询结果中 comm 列,一般职员没有奖金,而销售时有奖金的.

这时统计销售人员的薪金就需要加上奖金

idle> select ename,job,sal,comm,sal+comm from emp where ename in ('SMITH','WARD');

ENAME    JOB    SAL       COMM   SAL+COMM

---------- --------- ---------- ---------- ----------

SMITH    CLERK    800

WARD    SALESMAN   1250        500 1750

idle>

结果显示 sal + comm为NULL的值 还为NULL

我们再去打印COMM为NULL值的员工工资

idle> select ename,sal from emp where comm = NULL;

no rows selected

idle> 

结果却是没有选中

可见,NULL值为未知 不可以参与运算和比较

正确的写法如下:

idle> select ename,sal,comm from emp where comm is  NULL;

ENAME   SAL    COMM

---------- ---------- ----------

SMITH   800

JONES  2975

BLAKE  2850

CLARK  2450

SCOTT  3000

KING  5000

ADAMS  1100

JAMES   950

FORD  3000

MILLER  1300

10 rows selected.

idle> 

idle> select ename,sal,comm from emp where comm is not  NULL;

ENAME   SAL    COMM

---------- ---------- ----------

ALLEN  1600     300

WARD  1250     500

MARTIN  1250    1400

TURNER  1500       0

idle> 

如果要做运算 可以用NVL NVL2 nvlif函数先置为0

NVL(表达式1,表达式2)

如果表达式1是NULL,则返回表达式2

如果表达式1非NULL,则返回表达式1

表达式1和表达式2可以是数字,字符串,日期格式,1和2的数据类型必须一致

idle> select ename,job,sal,comm,sal+NVL(comm,0) from emp where ename in ('SMITH','WARD');

ENAME    JOB    SAL       COMM SAL+NVL(COMM,0)

---------- --------- ---------- ---------- ---------------

SMITH    CLERK    800        800

WARD    SALESMAN   1250        500      1750

idle> 

NVL2(expr1,expr2,expr3)

如果expr1为空,则返回expr3,否则返回expr2

idle> select ename,job,sal,comm,sal+NVL2(comm,comm,0) from emp where ename in ('SMITH','WARD');

ENAME    JOB    SAL       COMM SAL+NVL2(COMM,COMM,0)

---------- --------- ---------- ---------- ---------------------

SMITH    CLERK    800      800

WARD    SALESMAN   1250        500    1750

idle> 

NULLIF(expr1,expr2)

如果expr1和expr2相同,则返回空,否则返回expr1

idle> select ename,NULLIF(ename,'KING') from emp where ename in ('SCOTT','KING');

ENAME    NULLIF(ENA

---------- ----------

SCOTT    SCOTT

KING

idle> 

空值的排序 升序会排在最后 降序排在最前

idle> select ename,sal,comm from emp where ename in ('KING','WARD') order by comm;

ENAME   SAL    COMM

---------- ---------- ----------

WARD  1250     500

KING  5000

idle> select ename,sal,comm from emp where ename in ('KING','WARD') order by comm desc;

ENAME   SAL    COMM

---------- ---------- ----------

KING  5000

WARD  1250     500

idle> 

COALESCE(expr1,expr2,expr3,...)

返回第一个非空的表达式

SQL> select coalesce('','a','','b') from dual;

C

-

a

SQL> select coalesce('','','','b') from dual;

C

-

b

SQL> 

nvl nvl2