Sql与oracle中null值

1 null值的介绍 NULL 是数据库中特有的数据类型，当一条记录的某个列为 NULL ，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此， NULL 并不是一个确定的值。 这是 NULL 的由来、也是 NULL 的基础，所有和 NULL 相关的操作的

　　1 null值的介绍

　　NULL 是数据库中特有的数据类型，当一条记录的某个列为 NULL ，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此， NULL 并不是一个确定的值。 这是 NULL 的由来、也是 NULL 的基础，所有和 NULL 相关的操作的结果都可以从 NULL 的概念推导出来。

　　2 oracle中的null值介绍

　　在不知道具体有什么数据的时候，即未知，可以用NULL， 称它为空，ORACLE中，含有空值的表列长度为零。允许任何一种数据类型的字段为空，除了以下两种情况：

　　a、主键字段(primary key);

　　b、定义时已经加了NOT NULL限制条件的字段

　　3 Oracle中null值说明：

　　a、等价于没有任何值、是未知数。

　　b、NULL与0、空字符串、空格都不同。

　　c、对空值做加、减、乘、除等运算操作，结果 仍为空。

　　d、NULL的处理使用NVL函数。

　　e、比较时使用关键字用“is null”和“is not null”。

　　f、空值不能被索引，所以查询时有些符合条件的数据可能查不出来， count(expr)中，用nvl(列名,0)处理后再查。

　　g、排序时比其他数据都大(索引默认是降序排列，小→大)， 所以NULL值总是排在最后。

　　IS NULL 和IS NOT NULL 是不可分割的整体，改为IS 或IS NOT都是错误的，从上面我们看到了NULL 和空字符串的区别。

　　任何和NULL 的比较操作，如<>、=、<=等都返回UNKNOWN(这里的unknown就是null，它单独使用和布尔值false类似).判断和比较规则总结如下：

　　判断和比较规则总结表

　　例如：使用方法：

　　SQL> select 1 from dual where null=null;

　　没有查到记录

　　SQL> select 1 from dual where null="';

　　没有查到记录

　　SQL> select 1 from dual where ''='';

　　没有查到记录

　　SQL> select 1 from dual where null is null;

　　1

　　---------

　　1

　　SQL> select 1 from dual where nvl(null,0)=nvl(null,0);

　　1

　　---------

　　1

　　4、null做一些算术运算，比如+，-，*，/等，结果 还是null，但是对于连接操作符||，null忽略,concat函数也忽略null

　　SQL> select null || 'abc' from dual;

　　NUL

　　---

　　abc

　　SQL> select concat(null,'abc') from dual;

　　CON

　　---

　　abc

　　SQL> select null+10 from dual;

　　NULL+10

　　----------

　　5、null相关函数规则

　　Oracle有nvl、nvl2、nullif、coalesce等函数专门处理null

　　5.1 nvl(expr1,expr2)

　　描述：如果expr1是null，那么用expr2作为返回值，不是null则返回expr1.expr1与expr2一般是类型相同的，如果类型不同则会采用自动转换，转换失败则报错。

　　SQL*PLUS中数值类型右对齐，字符左对齐;通过第三条语句可以看出null和‘’还是有区别的。

　　SQL> select nvl(null,0) from dual;

　　NVL(NULL,0)

　　-----------

　　0

　　SQL> select nvl(to_char(null),0) from dual;

　　N

　　-

　　0

　　SQL> select nvl('',0) from dual;

　　N

　　-

　　0

　　5.2 nvl2函数

　　语法：nvl2(expr1,expr2,expr3)

　　描述：expr1如果是null，则返回expr3,否则返回expr2

　　expr2和expr3类型不同，expr3类型转换为expr2类型

　　SQL> select nvl2(null,'1',2) from dual;

　　N

　　-

　　2

　　expr2为null，返回值类型和expr3一致

　　SQL> select nvl2(null,null,2) from dual;

　　NVL2(NULL,NULL,2)

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

　　2

　　SQL> select nvl2(null,null,'2') from dual;

　　N

　　-

　　2

　　不同类型之间的转换，自动转换不成功，则报错

　　SQL> select nvl2(null,1,'b') from dual;

　　select nvl2(null,1,'b') from dual

　　*

　　ERROR at line 1:

　　ORA-01722: invalid number(无效数字)

　　5.3 nullif函数

　　语法：nullif(expr1,expr2)

　　描述：判断expr1和expr2是否相等，若相等则返回null，否则返回expr1.要求expr1与expr2类型必须相同

　　SQL> select nullif(1,3) from dual;

　　NULLIF(1,3)

　　-----------

　　1

　　SQL> select nullif(1,1) from dual;

　　NULLIF(1,1)

　　-----------

　　SQL> select nullif('1',1) from dual;

　　select nullif('1',1) from dual

　　*

　　ERROR at line 1:

　　ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

　　SQL> select nullif('ab','ab ') from dual;

　　NU

　　--

　　SQL> select nullif(null,1) from dual;

　　select nullif(null,1) from dual

　　*

　　ERROR at line 1:

　　ORA-00932: inconsistent datatypes: expected - got CHAR

　　SQL> select nullif(to_char(null),'1') from dual;

　　N

　　-

　　5.4 coalesce函数

　　语法：coalesce(expr1,expr2,…,exprn)

　　描述：从左到右返回第一个为非null的值，若所有的列表元素都为null，则返回null。要求所有都必须为同一类型。

　　SQL> select coalesce(null,null,null) from dual;

　　C

　　-

　　SQL> select coalesce(null,1,2) from dual;

　　COALESCE(NULL,1,2)

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

　　1

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

　　select coalesce(1,'1',1) from dual

　　*

　　ERROR at line 1:

　　ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

　　6 null与索引

　　Oracle中的B*Tree索引，并不存储全为null的列，