Sql与oracle中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的列，
虽然在表中建立了符合UNIQUE 索引，但是全为null的行还是可以插入的，而不是全为null的重复行则不可以插入。因为在UNIQUE约束中，（null，null）和（null，null）是不同的，当然在其他一些情况，比如说分组、集合操作中都认为全是null是相等的
SQL> create table t(a number,b number);

Table created.

SQL> create unique index idx_t on t(a,b);

Index created.

SQL> insert into t values(null,null);

1 row created.

SQL> insert into t values(1,null);

1 row created.

SQL> insert into t values(null,1);

1 row created.

SQL> commit;
SQL> insert into t values(1,null);
insert into t values(1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_T) violated

7 null的排序
  
  order by默认升序（asc）,这时候null是排在最后的，如果指定降序那么null是排在最前面的，认为null最大。
但是可以用nulls first和nulls last进行调整。
SQL> select * from emp
  2  order by comm asc;
SQL> select * from emp
  2  order by comm desc;
SQL> select *from emp
  2  order by comm asc nulls first;
SQL> select *from emp
  2  order by comm desc nulls last;

8  null与性能的关系

Not null约束，定义约束是要付出消耗性能的代价的，由下面的测试可以看出虽然约束检查的很快，但是有时候还是很消耗资源的，至少在这个例子上是这样的，不需要not null约束，除非必要，不要乱定义约束。
SQL> set serveroutput on
SQL> declare
  2  v_value number not null :=0;
  3  start_time number;
  4  end_time number;
  5  begin
  6  start_time :=DBMS_UTILITY.GET_TIME;
  7  FOR i in 0..100000000 LOOP
  8  v_value :=i;
  9  end LOOP;
 10  end_time :=DBMS_UTILITY.GET_TIME;
 11  DBMS_OUTPUT.PUT_LINE(end_time-start_time);
 12  END;
 13  /
1043

PL/SQL procedure successfully completed.

SQL> declare
  2  v_value number;
  3  start_time number;
  4  end_time number;
  5  begin
  6  start_time :=DBMS_UTILITY.GET_TIME;
  7  FOR i IN 0..100000000 LOOP
  8  v_value :=i;
  9  end LOOP;
 10  end_time :=DBMS_UTILITY.GET_TIME;
 11  DBMS_OUTPUT.PUT_LINE(end_time-start_time);
 12  END;
 13  /
767

9 动态语句中的绑定变量与null

在PL/SQL中动态SQL和动态PL/SQL经常使用绑定变量，这个绑定变量有个要求，就是不能直接传入字面量null值，因为PL/SQL中动态语句要求传入的绑定变量必须是SQL类型，而字面量null是无类型的，null字面量传入是不可以的。当然可以采用多种方法，如果一定要传入null，则可以将null改为空字符串、TO_NUMBER,TO_CHAR,TO_DATE等函数进行转换，或定义一个未初始化的变量、直接传入变量等。
SQL> create table test(id number,name varchar2(10),birth date);

Table created.

SQL> insert into test values(1,‘aa‘,SYSDATE);

1 row created.

SQL> insert into test values(null,‘aa‘,SYSDATE);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> declare 
  2  v_sql varchar2(4000);
  3  begin
  4  v_sql :=‘update test set birth=:vbirth where id is null‘;
  5  execute immediate v_sql using null;
  6  commit;
  7  end;
  8  /
execute immediate v_sql using null;
                              *
ERROR at line 5:
ORA-06550: line 5, column 31:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
SQL> declare
  2  v_sql varchar2(4000);
  3  begin
  4  v_sql :=‘update test set birth=:vbirth where id is null‘;
  5  execute immediate v_sql using ‘‘;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

10 sqlserver 中null值介绍
创建示例表， Script 如下：
create table dbo. cassaba_null
(
         column1         nvarchar ( 50)            not null,
         column2         nvarchar ( 50)            null
)
go
 
insert into dbo. cassaba_null values ( ‘1‘ , null)
insert into dbo. cassaba_null values ( ‘2‘ , ‘string‘ )
insert into dbo. cassaba_null values ( ‘3‘ , ‘‘ )
go

a.  使用 =null   / <>null       默认情况下的确不能使用 =null / <> null 来判断 null 值如此。实际上 SQL Server 可以 使用 SET ANSI_NULLS   { ON | OFF } 设定来控制 =null / <>null 的行为。
     当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。
     即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行
     
但是当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵守 ISO 标准。
      使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。
      使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。
      此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。


b.   改变 null 值的连接行为  
   SQL Server 提供 SET CONCAT_NULL_YIELDS_NULL 
{ ON | OFF } 来控制 null 与其它字符串连接的行为。
    当 SET CONCAT_NULL_YIELDS_NULL 为 ON 时，串联空值与字符串将产生 NULL 结果。例如， SELECT ‘abc‘ + NULL 将生成 NULL 。
    当 SET CONCAT_NULL_YIELDS_NULL 为 OFF 时，串联空值与字符串将产生字符串本身（空值作为空字符串处理）。例如， SELECT ‘abc‘ + NULL 将生成 abc 。
    如果未指定 SET CONCAT_NULL_YIELDS ，则应用 CONCAT_NULL_YIELDS_NULL 数据库选项的设置。
    注：在 SQL Server 的未来版本中， CONCAT_NULL_YIELDS_NULL 将始终为 ON ，而且将该选项显式设置为 OFF 的任何应用程序都将产生一个错误。
c.       变量的默认值与 null 值       
      命名一个变量后，如果没有给它赋初始值，它的值就是 null 。有时候需要注意初始 null 值和通过 select 语句给变量后期赋 null 的区别。因为此 ‘null’ 非彼 ‘null’ 。
d. 子查询中的 null
     子查询中出现的 null 值经常会被我们忽视，先查看下面的例子。

e. Case 语句中的 null   
Case 中的 when 语句注意不要写成 when null,   否则得不到想要的结果。
下面的第 1 条 sql 错误， 2 ， 3 正确。


f.  与 null 相关的函数 ISNULL ISNULL 检测表达式是否为 NULL ，如果是的话替换 NULL 值为另外一个值 COALESCE COALESCE 函数返回指定表达式列表的第一个非 NULL 值 NULLIF 当指定的两个表达式有相同值的时候 NULLIF 返回 NULL 值，否则返回第一个表达式的值
示例 1 ： set ansi_nulls on
declare @test1 nvarchar ( 10)
 
if ( @test1 = null)
         select 1
else
         select 2
---------------------------------------------------------------------
结果返回 2
 
示例 2 ： set ansi_nulls off
declare @test1 nvarchar ( 10)
 
if ( @test1 = null)
         select 1
else
         select 2
---------------------------------------------------------------------
结果返回 1
 
示例 3 ：
set ansi_nulls on
select * from dbo. cassaba_null where column2 != null
---------------------------------------------------------------------
无记录返回
 
示例 4 ：
set ansi_nulls off
select * from dbo. cassaba_null where column2 != null
---------------------------------------------------------------------
返回第 2 ， 3 条记录


如果不希望每次都判断 null, 可以使用 isnull 函数来达到每次把 null 自动替换为空字符串进行连接的效果。
示例 5 ：
declare @test nvarchar ( 50)
select isnull ( @test, ‘‘ ) + ‘extend‘
go
 
select   column1 + isnull ( column2, ‘‘ ) as column3 from dbo. cassaba_null
go
---------------------------------------------------------------------


示例 6 ：
declare @test nvarchar ( 50)
-- 无符合条件的语句 , 保持默认值 null
select @test= column2 from dbo. cassaba_null where column1 = ‘4‘
-- 有符合条件的语句，返回的栏位值为 null ，并且赋给 @test
select @test= column2 from dbo. cassaba_null where column1 = ‘1‘
    如果后面的代码使用 @test 的值是否为 null 来判断有没有匹配的记录，则可能发生错误。碰到这种状况，我们同样可以使用 isnull 函数来避免这个问题。
select @test= isnull ( column2, ‘‘ ) from dbo. cassaba_null where column1 = ‘1‘
    如上面的语句，即使有匹配的记录返回 null, 也会变成空字符串赋给 @test 了。这样就把两种情况区分开了。
示例 7 ： set ansi_nulls off
select * from cassaba_null a where a. column2 = ( select b. column2 from dbo. cassaba_null b where b. column1 = 1)
---------------------------------------------------------------------
         不管上面 ansi_nulls 设置为 on 还是 off ，始终没有记录返回。我们修改一下查询语句：
select * from cassaba_null a where a. column2 in ( select b. column2 from dbo. cassaba_null b where b. column1 = 1)
         这样，如果 ansi_nulls 设置为 on ， 则没有记录返回。 如果设置为 off ，则会返回一条记录。
         对于这种状况，如果我们确定不需要返回 null 值记录，则使用下面的 sql 语句：
select * from cassaba_null a where a. column2 in( select b. column2 from dbo. cassaba_null b where b. column1 = 1
and b. column2 is not null)
 
         反之，使用下面的语句：
select * from cassaba_null a where a. column2 in( select b. column2 from dbo. cassaba_null b where b. column1 = 1
and b. column2 is not null) or a. column2 is null


10 Sql与oracle中null值的不同
   
   a.在SQL Server中与oracle正相反，NULL值会被认为是一个无穷小的值，所以如果按照升序排列的话，则会被排在最前面
   b. SQL Server和Oracle中对插入数据值包含空的处理有所差异，在SQL Server中，我们可以把表字段设计为非空，但我们仍然可以通过下面语句执行插入操作：
      INSERT INTO Table (TestCol) VALUES(‘’)
其中的TestCol字段，在设计的时候，已经被设计为NOT NULL在sql server中，null和空格是不同的，也就是说，上面的语句插入的是一个空，但并不是NULL，只有当我们的插入语句中没有该字段的时候，才会被认为违反非空的条件约束，如果把NULL翻译成“空”的话，可能就会很容易搞混了。此外，如果我们的字段是INT类型的话，如果我们插入空的话，会得到一个0，也就是说，Sql server会自动帮我们处理对空格的转化。
但是在Oracle中，这个便利便不存在了，必须严格按照规则来进行插入，也就是说，我们再想视图通过插入空来满足NOT NULL的设计约束，已经不能成功了，必须插入实实在在的内容才能符合NOT NULL的约束。
   所以相对来说oracle要比sqlserver限制要多，也就相对严谨

Sql与oracle中null值,古老的榕树,5-wow.com