sqlserver null知多少

最新推荐文章于 2024-08-15 13:54:36 发布

Hehuyi_In

最新推荐文章于 2024-08-15 13:54:36 发布

阅读量1.9k

点赞数

原文链接：https://yq.aliyun.com/articles/228090

版权

一、 null是什么

不知道。我是说，它的意思就是不知道（unknown）。null与true、false组成谓词的三个逻辑值，但它没有明确的值，在不同的场景下，它能代表不同的含义。

下文以例子的方式给大家分享下null使用的典型场景及对应的用法。

二、 check约束与null

之前在SQL ServerCentral.com上看到一个关于check约束的null问题。

作者创建了一个表，在字段orderstatus上设置了check约束，只能插入指定的value，现在插入几行数据，其中有一行的value为null，问最终有几行可以插入成功。

create table Orders(OrderID int primary key,OrderTotal MONEY,OrderStatus VARCHAR(20)
constraint Orders_Status_Code check( OrderStatus in ('ACTIVE', 'INACTIVE', 'TBD')));

-- Now I want to insert data into the table. I run this batch.
insert Orders select 1, 435.43, 'Active';
insert Orders select 2, 554.66, 'InActive';
insert Orders select 3, 129.12, 'Not Active';
insert Orders select 4, 1228.00, NULL;

《T-SQL基础教程》中关于check约束与null的描述，著者用了一句言简意赅的口诀，check约束拒绝false，接受true和null

在上面的例子中：

当orderstatus为Avative和InActive时，check约束判断结果是true，插入成功
当为Not Active时，判断结果为false，插入不成功
当为Null时，判断的结果是null，插入成功。

所以，正确答案是3。

三、比较运算与null

null一个特殊性在于它无法比较和计算，null与任何值的任何比较和计算都等于null（is除外，unique约束除外，在unique约束中，同一个字段不允许出现两次null）。

判断null=null的结果是null，判断null<>null的结果也还是null。

先创建一个表，然后插入多行数据，其中有一行orderstatus的值为null

create table Orders(OrderID int primary key,OrderTotal MONEY,OrderStatus VARCHAR(20));

insert Orders select 1, 435.43, 'Active';
insert Orders select 2, 554.66, 'InActive';
insert Orders select 3, 129.12, 'Not Active';
insert Orders select 4, 1228.00, NULL;

现在我执行了where orderstatus<>'Active' 的查询，null所在的行会不会在查询结果里面？

select * from orders where OrderStatus<>'Active';

在上面的例子中：

当orderstatus为'InActive' 和'Not Active' 时，where条件判断的结果为true

当orderstatus为'null' 时，where OrderStatus<>'Active'等价于where null <>'Active'，而null与任何一个值的比较结果还是null，所以where条件判断的结果为null。

根据《T-SQL基础教程》描述，where筛选的原则是接受true，拒绝false和null。

所以orderstatus为null的行不会出现在结果集中。正确答案是：只会返回两行

四、 Not in与null和Not exists与null

not in和not exists都可以用来判断某个对象的存在与否，在大多数场景下两者可以相互替换，但在遇到null时，因为前者是三值逻辑（true|false|unknow）判断而后者只会返回true或false，因此处理的结果会有很大不同。

为了演示两者的区别，我们还是沿用上文的表，分别使用not in和not exists执行查询，找出OrderStatus不为Active和InActive的行。

if object_id(N’Orders’) is not null drop table orders
create table Orders(OrderID int primary key,OrderTotal MONEY,OrderStatus VARCHAR(20));

insert Orders select 1, 435.43, 'Active'
insert Orders select 2, 554.66, 'InActive'
insert Orders select 3, 129.12, 'Not Active'
insert Orders select 4, 1228.00, NULL

1. Not In与null

在下面这个查询中，使用not in来过滤数据，我们期望结果集中包含orderstatus为'Not Active'、'NULL'这两行的数据。

select * from orders where OrderStatus not in ('Active','InActive');

这个查询中，当OrderStatus为null时，原where子句等价于where null <>'Active' AND null<>'InActive'，这就变成了上文中介绍的比较运算与null的问题，where的判断结果还是null，所以该行不会出现在结果集中。而当OrderStatus为'Not Active'时，满足where筛选的为true的条件，会显示在结果集中。

最终，正确答案是：只有一行。

2. Not exists与null

现在我们还是期望结果集中包含orderstatus为'Not Active'、'NULL'这两行的数据，这次用Not exists。

在这个查询中，子查询先求出OrderStatus='Active' or OrderStatus='InActive'的行，然后外部查询用not exists过滤子查询的结果，将剩下的行显示在最终结果集中。

SELECT * FROM orders AS o1
WHERE NOT EXISTS(SELECT * FROM orders AS o2 
WHERE o1.OrderStatus = o2.OrderStatus AND (o2.OrderStatus = 'Active' OR o2.OrderStatus = 'InActive'));

其中内层子查询返回的结果集为：

然后我们再看外层查询，期望使用not exists返回orders表中不包含子查询结果集的行。也就是说，只要orders表没有子查询结果集中的行就返回true，否则返回false（只有存在和不存在，没有unknown的说法）。

按照这个逻辑，orderID为3和4的行不在子查询的结果集中，因此not exists判断为true，而orderID为1和2的行已包含在子查询的结果集中，所以not exists判断为false。根据where筛选接受true，拒绝false和null的原则，最终只有orderID为3和4的行显示在结果集中。