【Oracle】关于NULL，你是不是有很多问号？

最新推荐文章于 2021-04-06 01:40:23 发布

杨jun坚

最新推荐文章于 2021-04-06 01:40:23 发布

阅读量371

点赞数

分类专栏： Oracle 文章标签：数据库 oracle sql NULL

本文链接：https://blog.csdn.net/yangjjuan/article/details/106727842

版权

Oracle 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一，定义

1，NULL定义

官方文档中对NULL的定义
在这里插入图片描述
当一条记录的某个列为 NULL ，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此， NULL 并不是一个确定的值。 ORACLE中，允许任何一种数据类型的字段为空，除了以下两种情况：
a、主键字段（primary key）；
b、定义时已经加了NOT NULL限制条件的字段

2，NULL与三值逻辑

由于关系性数据库中增加的NULL，SQL中采用了一种特殊的逻辑体系——三只逻辑，即除了True和False二值外，还有unknown，对应于NULL的含义：未知。对应的真值表也有所不同。
在这里插入图片描述
三种逻辑运算中，NOT比较好理解，但是AND和OR，由于加入unknown后可能不太要理解，可以根据三值的优先级去判断AND和OR运算的结果，优先级高的决定运算的结果。
AND 的情况 False > unknown > True
OR 的情况 True > unknown > False

3，NULL与’’（空字符）

这里的‘’之间没有任何字符，这与‘ ’不同，中间还有一个空格。
各位Sqler在Oracle中都曾经写过 where col !=’'来做查询过滤，然而往往查询不到任何记录。
其实在Oracle中‘’ 与 null 是一样的，所以在使用 where col !=’’ 就相当于 where col !=null ，col !=null 结果就是unknown，因此，没有任何结果。 举个例子
创建成绩表 sc 插入学生id，分数score
在这里插入图片描述
现往表中再插入三条记录，两条score 为‘’，一条score为null

insert into sc values(8,'');
insert into sc values(9,'');
insert into sc values(10,null);

在使用语句查询这些新记录

select * from sc where score = ''   --预计返回id=8,9，实际无记录
select * from sc where score != ''  --预计返回id=1,2,3,4,5,6,7,10，实际无记录

select * from sc where score is null  --预计返回id=10，实际返回id=8,9,10
select * from sc where score is not null   --预计返回id=1,2,3,4,5,6,7,8,9，实际返回id=1,2,3,4,5,6,7

通过上面的例子，Oracle将插入的‘’(空字符)当作为null，所以在Oracle中可以直接将‘’(空字符)和nulll看作相同，具备和null相同的特性。

二，NULL的使用tips

1，NULL的判断只能定性，而不能定值，即 NULL不可用于比较大小，NULL与比较谓词（=、!=、>、<、>=、<=）的结果都是unknown，而在where/on/having和if/case when中，只有True才使条件成立。如果涉及and、or和not逻辑运算时，需要按照前面的真值表判断！！！！关于null 的判断只能使用is null 和 is not null

select * from sc where score != null --无记录返回

2，算数表达式中含有空值，则表达式结果为空。

SELECT 1+NULL+2 FROM dual   结果为null

3，聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行。

4， GROUP BY时，所有NULL被视为一组。

5，ORDER BY时，NULL被视为无穷大。

6，使用in 与 not in 时，需要注意子查询中的null，where 条件中的in与not in 都是先执行子查询后，再做根据条件过滤。

create table ta( id number(10), name varchar(20));
create table tb( id number(10), name varchar(20));

insert into ta (id, name) values (1, '张三');
insert into ta (id, name) values (2, '李四');
insert into ta (id, name) values (3, null);
insert into ta (id, name) values (4, '王五');

insert into tb (id, name) values (1, '张三');
insert into tb (id, name) values (2, '王五');
insert into tb (id, name) values (3, null)

select * from ta where name  in (select name from tb)  
/*
结果返回 张三  王五
name  in (select name from tb) 可以理解为：
name = '张三' or name = '王五' or name = null 等价于 name = '张三' or name = '王五' or  unknown
根据 OR 的情况 True > unknown > False ，只要结果中包含 True 就可以返回，所以只返回张三、王五 */

select * from ta where name  not in (select name from tb)  
/*
无记录返回
name not in (select name from tb) 可以理解为：
=> NOT (name ='张三' or name = '王五' or name = null) 
=> NOT name ='张三' AND NOT name = '王五' NOT name = null
=> name <> '张三' and  name <> '王五' and  name <> null   等价于 name <> '张三' and name <> '王五' and  unknown
根据 AND 的情况 False  >  unknown > True ，True优先级最低，所以where条件的结果都不为True
*/

7，使用 exists 和 not exists 时，同样注意null，得到的结果可能与in 、not in 不同。执EXISTS是对外表做LOOP循环，在循环体中执行内表查询，

select * from ta a where  exists (select 1 from tb b where b.name = a.name )
-- 结果返回 张三  王五
/*
for  name  in ta
   if select 1 from tb b where b.name = a.name 
     return name

相当于遍历 ta 中的记录，在执行子查询 select 1 from tb b where b.name = a.name ，
若有记录返回则 if 条件为真，返回当前ta的记录，只有张三和王五会返回记录，所以结果就是张三和王五
*/


select * from ta a where  not exists (select 1 from tb b where b.name = a.name )
--结果返回  null  李四
/*
for  name  in ta
   if not select 1 from tb b where b.name = a.name 
     return name
     
相当于遍历 ta 中的记录，在执行子查询 select 1 from tb b where b.name = a.name ，
只有当无记录返回时，if条件为真，返回当前ta的记录，只有null和李四未能查询到记录，所以结果就是null和李四
*/

8，使用 ANY 和 ALL 限定谓语时，同样注意null，不过应该较少使用到这两个谓语。

select * from ta where name  = ANY(select name from tb)  
--结果返回 张三  王五
/*name  = ANY(select name from tb)   可以理解为：
name = '张三' or name = '王五' or name = null 等价于 name = '张三' or name = '王五' or  unknown
根据 OR 的情况 True > unknown > False ，只要结果中包含 True 就可以返回，所以只返回张三、王五 */

select * from ta where name  = ALL(select name from tb)  
--无记录返回
/*name  = ALL(select name from tb)   可以理解为：
name = '张三' and name = '王五' and name = null 等价于 name = '张三' and name = '王五' and  unknown
根据 AND 的情况 False  >  unknown > True ，True优先级最低，所以where条件的结果都不为True */

三，NULL处理

在实际工作中，由于业务需求，难免会遇到null，Oracle 也提供了一些函数方法来处理null。

1，使用nvl(expr1,expr2) 处理NULL值

NVL
NVL函数用于返回两个表达式的非空值
语法：NVL(expression1, expression2)
参数：expression1 表达式
      expression2 表达
expression1和expression2类型可以不一致
返回：
如果expression1不为空，则返回expression1，如果expression1为空，则返回expression2

SELECT NVL('',1) FROM dual   -->1, 空字符串也是null

2，使用nvl2(expr1)处理NULL值

NVL2
NVL2函数根据表达式是否为空，返回不同的值
语法：NVL2(expression1, expression2, expression3)
参数：expression1 表达式
      expression2 表达式
      expression3 表达式
返回：
如果expression1不为空，则返回expression2的值，
如果expression1为空，则返回expression3的值。

注意：
1，expression1，可以为任意类型，expression2，expression3不可以long
2，expression2，expression3类型一致时，返回expression2的类型，如果为char,则返回varchar2
3，expression2，expression3类型不一致时，如expression3为空，则不转换类型，否则，将expression3
转换成expression2类型。

SELECT NVL2(' ',1,'s') FROM dual
--ORA-01722: 无效数字，无法将's'转成number类型  expression2，expression3类型不一致时，将expression3转换成expression2类型。

3，使用decode处理NULL值

decode
根据表达式是否为空，返回不同的值
语法：decode(expression,value,result1,result2)
如果expression=value，则输出result1，否则输出result2，result1和result2的数据类型可以不同，
在实际应用时，将value 设置为 null，即：
decode(expression,null,result1,result2)  -- 如果expression为null，则输出result1，否则输出result2,

SELECT decode(score,NULL,'成绩缺失',score) FROM  sc -->1

4，使用Coalesce处理NULL值

COALESCE函数用于返回其参数中第一个非空表达式。
语法：COALESCE(expression [ ,...n ])
参数：
expression 表达式
n 可以指定多个表达式，所有表达式必须是相同类型。
返回：
从左往右返回第一个不为空的值，如果所有表达式均为空，则返回空。

SELECT coalesce(NULL,NULL,1,2) FROM  dual -->1
SELECT coalesce(NULL,NULL,1,'a') FROM  dual
--ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR