Sql Server 中的=null与is null

最新推荐文章于 2024-07-18 21:44:35 发布

xiaoyong322

最新推荐文章于 2024-07-18 21:44:35 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： null sql server 数据库 table 存储脚本

本文链接：https://blog.youkuaiyun.com/dfyong/article/details/7010972

数据库专栏收录该内容

30 篇文章

订阅专栏

本文探讨了SQL Server中处理NULL值的方法，包括DECLARE声明变量时的行为、SET为变量赋值的过程，以及=NULL和IS NULL的区别。强调了在不同情况下选择合适方法的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先明白一件事

当我们在 sql server 中用 DECLARE 申明一个变量时， sql server 将会把变量存储在SQLs 内存空间的变量列表区域（variable table），变量列表中包含了变量的名称和存储地址。然而如果我们在创建变量的时候并没有给变量赋值的话，sql server是没有为变量分配内存的，因此变量并没有在内存中被定义。
然后当你用 SET 关键字给变量赋值的话，sql server 将为这个值分配内存空间并将地址存放在变量列表（variable table）中。如果你再次为这个变量赋值的话新的内存地址将替换旧的地址。

下面我们就来讨论一下 “= NULL”和“IS NULL”的区别。

“= NULL”

“= NULL”是值表达式。意味着，这个表达式会判断是否已经为变量正确设置了值。事实上我们是可以设置一个变量的值为 NULL 的（如果设置变量 = NULL，这就说明变量的值是未知的 unknown）。参考下面的代码。

DECLARE @val CHAR(4)

SET @val = NULL

我们显式给变量赋值 NULL，sql server会为变量分配内存空间并指出变量值是未知的 unknown，因此下面的表达式将返回 true（因为我们已经通过 SET @val = NULL 为变量分配了内存空间）：

If @val = NULL

但如果我们像下面的代码段一样在申明变量的时候不给变量赋值：

DECLARE @val CHAR(4)

If @val = NULL

表达式将返回 false。

导致这种情况的原因是，在没有给变量赋值的情况下，sql server 是不会为变量分配内存空间，因此地址是未知的就无法进行值的比较了。

Note: 上面的示例结果与 ANSI_NULLS (ON|OFF) 的设置有关。

“IS NULL”
“IS NULL”的情况就有点微妙，在需要比较变量值是不是为 NULL 的时候它应该是首选的用法。IS NULL 会同时检查变量地址和变量的地址所指向的值是不是未知的（unknown）。
研究一下下面的代码：
•
DECLARE @val CHAR(4)

•
If @val IS NULL

•
PRINT 'TRUE'

•
ELSE

•
PRINT 'FALSE'

•
SET @val = NULL

•
If @val IS NULL

•
PRINT 'TRUE'

•
ELSE

•
PRINT 'FALSE'

上面两个表达式都会返回 TRUE，原因是 IS NULL 同时比较地址和值是不是未知的。

ANSI_NULLS
SET ANSI_NULLS (ON|OFF)
环境变量 SET ANSI_NULLS (ON|OFF) 的设置对 “= NULL”的表达式会有很大的影响，下面的段落摘录自sql server 的帮助，它很好的解释了这一因素。（SET ANSI_NULLS 详细的说明参考sql server 帮助）

SQL-92 标准要求对空值的等于 (=) 或不等于 (<>) 比较取值为 FALSE。当 SET ANSI_NULLS 为 ON 时，即使 column_name 中存在空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中存在非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍返回零行。

当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中含有空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中含有非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有非 XYZ 值和非 NULL的行。

当 SET ANSI_NULLS 为 ON 时，所有对空值的比较均取值为 UNKNOWN。当 SET ANSI_NULLS 为 OFF 时，如果数据值是 NULL，则所有数据对空值的比较将取值为 TRUE。如果未指定，则应用当前数据库的 ANSI nulls 选项的设置。

为使脚本按预期运行，不管 ANSI nulls 数据库选项或 SET ANSI_NULLS 的设置是什么，在可能包含空值的比较中使用 IS NULL 和 IS NOT NULL。

所以我们在需要判断NULL的时候最好使用 IS NULL 和 IS NOT NULL。尽量避免使用 = NULL 和 <> NULL，因为后者会产生非预期的效果。

在 SET ANSI_NULLS 设置不同时下面的 sql 语句会产生不同结果：

SELECT * FROM t1 WHERE a = NULL

SELECT * FROM t1 WHERE a <> NULL

SELECT * FROM t1 WHERE a IS NULL