NULL与三值逻辑

小结

• 三值逻辑: true false unknown
• NULL 与值进行运算操作, 结果是 NULL
• NULL 与值进行比较操作, 结果是 unknown
• 在where/on/having和if/case when 等中，三值逻辑中只有True才使条件成立
• 聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行
• 谨慎使用 not in + 子查询

三值逻辑含义

通常逻辑只有 真假(true / false), 如 java / c 等程序, 三只逻辑指的是逻辑有三个值: true / false / unknown(未知)

背景

关于SQL是否应该允许NULL，有很大争议

两派的意见

主张使用的原因

• 当需要表示一个未知的、不确定的值时，用 NULL 更自然。比如一个现在职员工的离职时间、顶级员工（BOSS）的上级员工，等等。
• NULL 无可避免, 外联接通常会引入 NULL，即使所有表的字段都定义为 NOT NULL。

不主张使用的原因

• NULL 会引入复杂的三值逻辑
• NULL在查询条件、外键和CHECK约束、唯一约束、GROUP BY、ORDER BY中的行为都是不一致的

最终 NULL 还是保留了下来

T-SQL 标准

• 运算: NULL 与值进行运算操作, 结果是 NULL

3 + NULL = NULL; 10 * NULL = NULL; 10 / NULL = NULL

• 比较: null 与值进行比较操作, 结果是 unknown

3 >= null 值为 unknown ; 3 =< null 值亦为 unknown

• 过滤: 在where/on/having和if/case when 等中，三值逻辑中只有True才使条件成立

-- null 与值进行比较操作, 结果是 unknown
select 1 where 1=NULL
select 1 where 1<>NULL

-- NULL 与值进行运算操作, 结果是 NULL
select 1 where 1+NULL >= 1
select 1 where 1+NULL <= 1

• 分组: group by 时，所有NULL被视为一组; 聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行

• 排序: order by时, 所有NULL排在一起, 但NULL排在非空值的前面(如SQL Server)还是后面(如Oracle)，SQL标准未规定

• 声明: declare的变量，在未赋值之前为NULL

• 约束: 包含外键约束和Check约束的字段允许NULL(即约束只当条件为False时出错，Unknown是不管的); 包含唯一约束(unique index)的字段只允许一个NULL的行，再插入或更新该字段为NULL的行会报字段重复的错误。

• 判断值是否为 NULL: is null / is not null

千万不要用 等号 (=) 来判断一个值是否为 NULL, 想想为什么

• 不同的数据库厂商(sql/mysql/…)对 null/unknown 的处理不一定相同

建议

定义表结构时, 尽量使用 NOT NULL 约束

• 字符串类型必须非空约束, 以空串代表空值
• 布尔值类型必须非空约束, 默认 true 或 false
• 数字 / 时间类型能用非空约束就用非空约束

在使用NULL时，一定要搞清楚三值逻辑和数据库引擎对NULL的处理

MSSQL 对 null 的处理

ANSI_NULLS

SET ANSI_NULLS { ON | OFF }

指定在 SQL Server 2017 中与 Null 值一起使用等于 (=) 和不等于 (<>) 比较运算符时采用符合 ISO 标准的行为。

当 ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。 即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍返回零行。
当 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 的行。
当 ANSI_NULLS 为 ON 时，所有对 null 值的比较均取值为 UNKNOWN。

SET ANSI_NULLS (Transact-SQL)

练手

/*
create database Test
go

use Test
go

drop table tUser
go

create table tUser
(
    sID nvarchar(40) not null,
    sUserNO nvarchar(20) null,
    sUserName nvarchar(40) null,
    sPassword nvarchar(40) null,
    dLastUpdateTime datetime not null
)


insert into tUser
        (sID, sUserNO, sUserName, sPassword, dLastUpdateTime)
values  (newid(), null, 'test01', '', getdate()),
        (newid(), null, 'test02', null, getdate()),
        (newid(), 'userno01', 'test03', null, getdate()),
        (newid(), 'userno02', 'test04', null, getdate())

select * from tUser
*/

-- null 与值进行运算操作, 结果是null
select null + 10, 10 - null, null * 10, null / 10

-- 在where/on/having和if/case when中，三值逻辑中只有True才使条件成立
select *
from tUser as u
where u.sUserNO = ''

select *
from tUser as u
where u.sUserNO <> ''

-- group by 时，所有NULL被视为一组
select u.sUserNO, num = count(1)
from tUser as u
group by u.sUserNO

-- 聚集函数（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行
select u.sUserNO, userNum = count(sUserNO), num = count(1)
from tUser as u
group by u.sUserNO

-- order by时, 所有NULL排在一起, 但NULL排在非空值的前面(如SQL Server)还是后面(如Oracle)，SQL标准未规定
select *
from tUser as u
order by u.sUserNO

-- not 与 in / not in
-- not in (null, ...) 结果会变得意外, 故谨慎使用 not in + 子查询
select * from tUser where sUserNO in ('userno01')
select * from tUser where sUserNO not in ('userno01')

select * from tUser where sUserNO in ('userno01', null)
select * from tUser where sUserNO not in ('userno01',null)

参考

书籍: Microsoft SQL SERVER 2008技术内幕 T-SQL查询 : 1.3 逻辑查询处理阶段详解 (第九页)