处理 T-SQL 中的 NULL

目录

• 前言
• NULL 的一般认知
• 什么是 NULL？
• 处理 NULL 值的函数和运算符
  1. ISNULL
  2. COALESCE
  3. NULLIF
  4. IS NULL 和 IS NOT NULL
• 关系运算符中的 NULL
• 条件运算符中的 NULL
  1. IN 和 NOT IN
  2. BETWEEN 和 NOT BETWEEN
• ORDER BY (排序) 中的 NULL
• GROUP BY 中的 NULL
• 聚合函数中的 NULL

前言

显示错误消息总比产生错误输出好。当处理 NULL 时，即使代码存在一些逻辑错误，也有可能产生输出。它在维护数据库输出的质量和准确性方面起着至关重要的作用。

在本文中，我想描述 T-SQL 中关于 NULL 的不同方面、一般认知和最佳实践。

非常感谢您的评论。:).

NULL 的一般认知

1. 它是空的。
2. 它是空白的。
3. 它是零。
4. 它是什么都没有。
5. 它是缺失值。
6. 它是最小值。
7. 它是一个可忽略的值。
8. 它是一个可选值。
9. 它是无效的。
10. 它是空的。

什么是 NULL？

NULL 意味着 “无结果” 或 “未知”，它不等于自身。

     NULL <> NULL  --NULL does not equals NULL.
     if(NULL=NULL) -- It returns nothing(no error and no result).
     NULL + Anything=NULL -- If you add anything to add, it always return NULL.

CODD 规则 3：对 null 值的系统化处理

DBMS 必须允许每个字段保持 null (或为空)。具体来说，它必须支持一种“缺失信息和不适用信息”的表示方法，该方法是系统化的，与所有常规值（例如，对于数值，“区别于零或任何其他数字”）不同，并且独立于数据类型。还隐含的是，此类表示必须由 DBMS 以系统化的方式进行操作。

Microsoft

NULL 值表示该值未知。NULL 值与空值或零值不同。两个 null 值不相等。两个 null 值之间的比较，或者 NULL 与任何其他值之间的比较，都会返回未知，因为每个 NULL 的值都是未知的。

Wiki

SQL NULL 是用于表示缺失值的术语。表中的 NULL 值是字段中看起来空白的值。具有 NULL 值的字段是没有值的字段。非常重要的是要理解 NULL 值与零值或包含空格的字段是不同的。

处理 NULL 值的函数和运算符

COALESCE 和 ISNULL T-SQL 函数用于返回输入参数中的第一个非 null 表达式。两者都用于处理 T-SQL 中的 NULL 值。ISNULL 接受两个参数，而 COALESCE 根据需要接受两个以上的参数。

 
     declare @x int=null;
     Select ISNULL(@x,'0') AS ISNULL_OUTPUT
     *************************************************** 
     Output : 
       ISNULL_OUTPUT
           0
     --@x is null that is replaced with 0
     *************************************************** 
 

     declare @x int=null;
	 declare @y int=null;
	 declare @z int=20;
     COALESCE(@x,@y,@z,'0') as COALESE_OUTPUT
     --*************************************************** 
     Output : 
       COALESE_OUTPUT
           20
     --First and second argument(@x and @y) are null so that COALESE return first 
     --non-NULL argument as 20(a value of @z)
     --***************************************************  

NULLIF

NULLIF 接受两个参数，如果参数为 NULL，则返回 NULL，否则返回第一个参数。

declare @x int=0;
select NULLIF(@x,0) as Result -- return NULL if @x is 0
--**************************************
Output
Result
NULL
--**************************************

IS NULL 和 IS NOT NULL

无法使用诸如 =、< 或 <> 等关系运算符来测试 NULL 值。为了检查值是否为 NULL，我们需要使用 IS NULL 或 IS NOT NULL 运算符。

 declare @value int=null;
 if @value is NULL
 begin
   Select 'Value is NULL' AS Result
 end

 set @value=1
 if @value is NOT NULL
 begin
   Select 'Value is not NULL' As Result
 end

关系运算符中的 NULL

• NULL 不等于任何值
• NULL 不大于、不小于或不等于值
• NULL 不等于它自己。
• NULL 不大于、不小于或不等于 NULL

条件运算符中的 NULL

IN 和 NOT IN

• IN 不会对值为 NULL 的值返回匹配项。
• 如果至少有一个值为 NULL，则 NOT IN 返回 false。

declare @Temp table(
	  Col1 int,
	  Col2 varchar(20)
  	)
insert into @Temp values(1,'Test 1')
insert into @Temp values(2,'Test 2')
insert into @Temp values(3,'Test 3')
insert into @Temp values(4,'Test 4')
insert into @Temp values(5,'Test 5')
insert into @Temp values(null,'Test 6')
insert into @Temp values(7,'Test 7')
insert into @Temp values(null,'Test 8')
insert into @Temp values(null,'Test 9')
insert into @Temp values(10,'Test 10')

select * from @Temp where Col1 in(1,3,6)
--It returns 3 rows

select * from @Temp where Col1 in(1,3,6,null)
--It does not include null so it returns 3 rows 

select * from @Temp where Col1 not in(1,3,6,null)
-- it does not return any result 

BETWEEN 和 NOT BETWEEN

• 如果任一边界值为 NULL，则 BETWEEN 和 NOT BETWEEN 返回 false。

	declare @Temp table(
	  Col1 int,
	  Col2 varchar(20)
  	)
insert into @Temp values(1,'Test 1')
insert into @Temp values(2,'Test 2')
insert into @Temp values(3,'Test 3')
insert into @Temp values(4,'Test 4')
insert into @Temp values(5,'Test 5')
insert into @Temp values(null,'Test 6')
insert into @Temp values(7,'Test 7')
insert into @Temp values(null,'Test 8')
insert into @Temp values(null,'Test 9')
insert into @Temp values(10,'Test 10')

select * from @Temp where Col1 between 1 and 7
--Returns 6 rows
select * from @Temp where Col1 between null and 7
--No result
select * from @Temp where Col1 between 1 and null
--No result
select * from @Temp where Col1 not between 1 and 7
--Returns 1 row
select * from @Temp where Col1 not between null and 7
--Returns 1 row
select * from @Temp where Col1 not between 1 and null
--No result

ORDER BY (排序) 中的 NULL

在排序顺序中，NULL 是最小值。

GROUP BY 中的 NULL

在执行 group by 时，NULLs 被视为相等。如果 GROUP BY 子句中的列包含带有 NULL 的行，则这些行将被分组到一个组中。

聚合函数中的 NULL

聚合函数 – COUNT、SUM、AVG、MAX、MIN 和 LIST – 不处理 NULL。此规则有一个例外：COUNT(*) 返回所有行的计数，即使是字段全部为 NULL 的行。但是 COUNT(FieldName) 的行为与其他聚合函数类似，它只计算指定字段非 NULL 的行。

历史

• 2014-10-04：初始版本