为什么不“WHERE column = NULL"?在 SQL Server 中引发错误? [英] Why doesn't "WHERE column = NULL" throw an error in SQL Server?

问题描述

可能的重复:
在where子句+SQL Server中IS NULL vs = NULL

考虑下面的代码——有人可以向我解释为什么一个查询没有抛出错误吗?我可以理解为什么它不返回记录，但它想做什么?请记住，colA 是一个整数.它在 2000、2005 和 2008 r2 上的工作原理相同

Consider the following code – can someone explain to me why the one query doesn’t throw an error? I can understand why it doesn’t return a record, but what is it trying to do? Remember that colA is an int. It works the same on 2000,2005 and 2008 r2

create table #foo
( colA int )


insert into #foo
(colA)
values
(null)

select * from #foo --returns the one record we just created

select * from #foo where colA = null --does not throw an error and does not return a record! why??
select * from #foo where colA is null --returns the record

drop table #foo

该表中是否存在会返回 colA = null 的记录?

Is there ever record that could exist in this table that would return for colA = null?

我目前没有任何其他数据库可供我试用 - 这是标准还是特定于 MSSQL 的行为?

I dont have any other databases available to me at the moment to try this out - is this standard or is it behavior specific to MSSQL?

推荐答案

那是因为 NULL 不能等同于任何值.

That is because a NULL can't be equated to any value.

禁用 ANSI_NULLS 选项，然后运行它，您现在将看到该行:

Disable the ANSI_NULLS option and then run it you will see the row now:

SET ANSI_NULLS OFF
select * from #foo --returns the one record we just created  
select * from #foo where colA = null --does not throw an error and does not return a record! why?? 
select * from #foo where colA is null --returns the record  drop table #foo 

这篇关于为什么不“WHERE column = NULL"?在 SQL Server 中引发错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！