首页 > 代码库 > 不再迷惑,无值和NULL值

不再迷惑,无值和NULL值

在关系型数据库的世界中,无值和NULL值的区别是什么?一直被这个问题困扰着,甚至在写TSQL脚本时,心有戚戚焉,害怕因为自己的一知半解,挖了坑,贻害后来人,于是,本着上下求索,不达通幽不罢休的决心(开个玩笑),遂有此文。

学习过关系型数据库的伙伴都知道,NULL是指不确定的值,在数据库中绝对是噩梦的存在;而空值,一般对字符串类型而言,指没有任何值的字符串类型,为字符类型的变量设置为空值:set @vs=‘‘,空值跟无值不同。有人可能会问,无值是什么?无值,是指数据表中没有任何数据。无值和不确定值,单从字面意思上来看,两者之间的定义很清楚,一旦深究,这两者之间的关系,有时令人十分迷惑(confused),这是因为,在特定条件下,无值会转换为NULL值。

一,举个栗子,理解无值和NULL值的区别

比如,创建一个临时表,在不插入任何数据时,该数据表是空的,没有任何值,对其执行select命令,将不会返回任何数据值:

create table #temp(id int null)

创建一个标量类型的变量,在不初始化时,该变量的值是不确定的,其值是NULL:

declare @vs int

创建一个表类型变量,在不初始化时,该表变量没有任何数据,是无值的:

declare @vt as table(id int null)

总结一下,声明一个标量型变量,如果没有对变量进行初始化,其值是不确定的,是NULL值;对于表变量,临时表和基础表,如果没有插入任何数据,该表没有任何数据,是无值的。

二,无值和NULL值的转换

在开始本节之前,先为变量赋值,简单的一个select命令就可以完成变量的赋值:

select @vs=1

有些朋友思维比较活跃,立马会想到:“用select命令可以从表中取值为变量赋值”,对,但是,赋值方法不是我求索的重点,我关注的是从表中取值为变量赋值的结果。

1,从空表中为变量赋值

如果数据表是空表,没有任何值,那么数据库引擎不会执行赋值语句,变量保持原有值不变:

select @vs=idfrom #temp

但是,如果采用以下方式,那么数据库引擎会执行赋值语句,由于空表不返回任何值,数据库引擎会把无值转换为不确定值NULL:

select @vs=(select top 1 idfrom #temp)

诧异吗?无值和NULL值的转换,居然从不起眼的变量赋值开始。注意,当不返回任何值时,数据库引擎不确定返回值,就把无值转换为NULL值。

2,从空表中计算聚合

空表是没有任何数据的表,计算聚合会产生怎样的结果?

select count(0) as count_all    ,count(id) as count_id    ,max(id) as max_id    ,min(id) as min_id    ,avg(id) as avg_id    ,sum(id) as sum_idfrom #temp

当统计数据行数时,返回的是0;当计算聚合函数(max,min,avg和sum)的聚合值时,由于无值可以聚合,数据库引擎不能确定这些聚合函数的返回值,因此,数据库引擎返回NULL值。

技术分享

三,聚合函数忽略NULL值 

一般情况下,除了count(0),count(*)之外,聚合函数都会忽略NULL值,而统计非NULL值,如果读者有疑问,可以查看我的博客《TSQL 聚合函数忽略NULL值》。如果只知聚合函数忽略NULL值,而不知空表也会产生结果为NULL的聚合值,轻易得出聚合函数不会返回NULL值的定论,那就很尴尬。楼主曾遇到过一次“意外”,在一次调试脚本代码的过程中,我遇到max聚合函数返回NULL值的情况,当时一脸懵逼,直接怀疑自己之前的所学。

当聚合列值都是NULL值时,由于聚合函数忽略NULL值,因此,当计算聚合函数(max,min,avg和sum)的聚合值时,由于无值可以聚合,数据库引擎不能确定这些聚合函数的返回值,因此,数据库引擎返回NULL值。

insert into #temp(id)values(null)select count(0) as count_all    ,count(id) as count_id    ,max(id) as max_id    ,min(id) as min_id    ,avg(id) as avg_id    ,sum(id) as sum_idfrom #temp

聚合函数(max,min,sum,avg和count)忽略null值,但不代表聚合函数不返回null值:如果数据表为空表,或聚合列值都是null,那么max,min,sum,avg聚合函数返回null值,而count 聚合函数返回0。聚合函数的共性:Null values are ignored。

 技术分享

 不再迷惑:当不返回任何值时,数据库引擎不确定返回值,就把无值转换为NULL值。

不再迷惑,无值和NULL值