22,209
社区成员
发帖
与我相关
我的任务
分享null值比较的问题
set ansi_nulls off
select null as a, null as b into #tmp
select 1 from #tmp where null = null
select 1 from #tmp where a=null
select 1 from #tmp where b=null
select 1 from #tmp where a = b
select 1 from #tmp where a =a
select 1 from #tmp where a = isnull(a, null)
set ansi_nulls on
前三句将返回数据，说明条件成立。后三句不返回数据，为什么？如何才能让两个皆为null的表达式相等？
select null as a, null as b into #tmp
select 1 from #tmp where null = null
select 1 from #tmp where a=null
select 1 from #tmp where b=null
select 1 from #tmp where a = b
select 1 from #tmp where a =a
select 1 from #tmp where a = isnull(a, null)
set ansi_nulls on
前三句将返回数据，说明条件成立。后三句不返回数据，为什么？如何才能让两个皆为null的表达式相等？
...全文
请发表友善的回复…
发表回复
Xcrow 2005-08-16
- 打赏
- 举报
表结构我是无权随意更改的，实际上我只是举个例子，真正的情况要比这复杂得多。在这个例子中我只能说，name就是主键。
Xcrow 2005-08-16
- 打赏
- 举报
所以我必须用 = 去比较 null, 呵呵，谢谢大家的帮助，我们继续研究
hsj20041004 2005-08-16
- 打赏
- 举报
你应该在表中增加一个主键列id 就可以了
表A:
id NAME SEX
1 acrow male
2 <null> female
表B:
id NAME AGE
1 acrow 31
2 <null> 30
SELECT * from A Left Join B On A.id = B.id
表A:
id NAME SEX
1 acrow male
2 <null> female
表B:
id NAME AGE
1 acrow 31
2 <null> 30
SELECT * from A Left Join B On A.id = B.id
Xcrow 2005-08-16
- 打赏
- 举报
修正上面的：
得到的应该是
NAME SEX AGE
acrow male 31
<null> female <null>
得到的应该是
NAME SEX AGE
acrow male 31
<null> female <null>
Xcrow 2005-08-16
- 打赏
- 举报
呵呵，这个我当然知道了。
我的目的是为了实现 Left Join
例如：
表A:
NAME SEX
acrow male
<null> female
表B:
NAME AGE
acrow 31
<null> 30
现在
SELECT * from A Left Join B On A.Name = B.Name
但是，因为 null 不可以用 = 比较， 所以得不到下面的结果
NAME SEX AGE
acrow male 31
<null> female 30
得到的是
NAME SEX AGE
acrow male 31
<null> <null> <null>
我该如何才能得到第一个结果呢
我的目的是为了实现 Left Join
例如：
表A:
NAME SEX
acrow male
<null> female
表B:
NAME AGE
acrow 31
<null> 30
现在
SELECT * from A Left Join B On A.Name = B.Name
但是，因为 null 不可以用 = 比较， 所以得不到下面的结果
NAME SEX AGE
acrow male 31
<null> female 30
得到的是
NAME SEX AGE
acrow male 31
<null> <null> <null>
我该如何才能得到第一个结果呢
子陌红尘 2005-08-16
- 打赏
- 举报
如果比较运算符"="两边都为表达式且其中一个表达式或两个表达式的值为NULL，不论是否设置ANSI_NULLS为OFF，比较的结果都将为 UNKNOWN。
hsj20041004 2005-08-16
- 打赏
- 举报
空 (NULL) 值表示数值未知。空值不同于空白或零值。没有两个相等的空值。比较两个空值或将空值与任何其它数值相比均返回未知，这是因为每个空值均为未知。
空值通常表示未知、不可用或将在以后添加的数据。例如，客户的中间名首字母在客户下定单时可能不知道。
下面是有关空值的一些信息：
若要测试查询中的空值，请在 WHERE 子句中使用 IS NULL 或 IS NOT NULL。
在 SQL 查询分析器中查看查询结果时，空值在结果集内显示为 (null)。
可通过以下方法在列中插入空值：在 INSERT 或 UPDATE 语句中显式声明 NULL，或不使此列进入 INSERT 语句，或使用 ALTER TABLE 语句在现有表中新添一列。
不能将空值用于区分表中两行所需的信息（例如，外键或主键）。
------在联机帮助中键入 is null查看
空值通常表示未知、不可用或将在以后添加的数据。例如，客户的中间名首字母在客户下定单时可能不知道。
下面是有关空值的一些信息：
若要测试查询中的空值，请在 WHERE 子句中使用 IS NULL 或 IS NOT NULL。
在 SQL 查询分析器中查看查询结果时，空值在结果集内显示为 (null)。
可通过以下方法在列中插入空值：在 INSERT 或 UPDATE 语句中显式声明 NULL，或不使此列进入 INSERT 语句，或使用 ALTER TABLE 语句在现有表中新添一列。
不能将空值用于区分表中两行所需的信息（例如，外键或主键）。
------在联机帮助中键入 is null查看
hsj20041004 2005-08-16
- 打赏
- 举报
select null as a, null as b into #tmp
null就是不确定，又怎么能相等呢(null = null)
判断一个字段为空用 is null
select * from #tmp where null = null --不能判断空值相等
select * from #tmp where a is null
select * from #tmp where b is null
select * from #tmp where a = b----不能判断空值相等
select * from #tmp where a =a ----不能判断空值相等
select * from #tmp where a = isnull(a, null) -----不能判断空值相等
null就是不确定，又怎么能相等呢(null = null)
判断一个字段为空用 is null
select * from #tmp where null = null --不能判断空值相等
select * from #tmp where a is null
select * from #tmp where b is null
select * from #tmp where a = b----不能判断空值相等
select * from #tmp where a =a ----不能判断空值相等
select * from #tmp where a = isnull(a, null) -----不能判断空值相等
hlq8210 2005-08-16
- 打赏
- 举报
学习一下
jzdmyjzd 2005-08-16
- 打赏
- 举报
值得学习，一起研究
Xcrow 2005-08-16
- 打赏
- 举报
顶一下，希望高手指点，谢谢了！
wjwboy 2005-08-16
- 打赏
- 举报
比较NULL 用 字段 is NULL
Xcrow 2005-08-16
- 打赏
- 举报
问题解决了，从一个朋友那里学到的，非常之简单。
Select * From A Left Join B On (A.name = B.name or (A.name is null And B.name is null))
前一个例子用下面的语句
select 1 from #tmp where a = null and b = null
尽管 a = b 是不成立的 但是 a = null and b= null 却是成立的
另外如果不用 set ansi_nulls off 的话
那么 select 1 from #tmp where a is null and b is null 就ok了。
这么简单的方法我怎么就没想到呢，呵呵。
Select * From A Left Join B On (A.name = B.name or (A.name is null And B.name is null))
前一个例子用下面的语句
select 1 from #tmp where a = null and b = null
尽管 a = b 是不成立的 但是 a = null and b= null 却是成立的
另外如果不用 set ansi_nulls off 的话
那么 select 1 from #tmp where a is null and b is null 就ok了。
这么简单的方法我怎么就没想到呢，呵呵。
Xcrow 2005-08-16
- 打赏
- 举报
不行啊，如果数据中刚好有个 Name = '' 的，岂不是乱了。
rivery 2005-08-16
- 打赏
- 举报
declare @t1 table( name varchar(20),sex varchar(20))
insert into @t1
select 'acrow','male' union
select NULL,'female'
declare @t2 table( name varchar(20),age int)
insert into @t2
select 'acrow',31 union
select NULL,30
SELECT * from @t1 a Left Join @t2 b On isnull(A.Name,'') = isnull(B.Name,'')
/*
不知这样能否满足你得要求。
result
NULL female NULL 30
acrow male acrow 31
*/
insert into @t1
select 'acrow','male' union
select NULL,'female'
declare @t2 table( name varchar(20),age int)
insert into @t2
select 'acrow',31 union
select NULL,30
SELECT * from @t1 a Left Join @t2 b On isnull(A.Name,'') = isnull(B.Name,'')
/*
不知这样能否满足你得要求。
result
NULL female NULL 30
acrow male acrow 31
*/
Xcrow 2005-08-16
- 打赏
- 举报
期待大家的高见，谢谢了
guid6 2005-08-15
- 打赏
- 举报
学习一下
Xcrow 2005-08-15
- 打赏
- 举报
呵呵，谢谢，一起研究！
点点星灯 2005-08-15
- 打赏
- 举报
具体请参考:联机帮助,我也说不清楚,不过楼主的这个问题还是很有点意思的,特别是理解NULL
点点星灯 2005-08-15
- 打赏
- 举报
注释
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的行。
说明 Microsoft® SQL Server™ 是将空字符串解释为单个空格还是真正的空字符串，取决于 sp_dbcmptlevel 的兼容级别设置。如果兼容级别小于或等于 65，SQL Server 就将空字符串解释为单个空格。如果兼容级别等于 70，则 SQL Server 将空字符串解释为空字符串。有关更多信息，请参见 sp_dbcmptlevel。
当 SET ANSI_NULLS 为 ON 时，所有对空值的比较均取值为 UNKNOWN。当 SET ANSI_NULLS 为 OFF 时，如果数据值是 NULL，则所有数据对空值的比较将取值为 TRUE。如果未指定，则应用当前数据库的 ANSI nulls 选项的设置。有关 ANSI nulls 数据库选项的更多信息，请参见 sp_dboption 和设置数据库选项。
为使脚本按预期运行，不管 ANSI nulls 数据库选项或 SET ANSI_NULLS 的设置是什么，在可能包含空值的比较中使用 IS NULL 和 IS NOT NULL。
对于存储过程，SQL Server 使用最初创建存储过程时的 SET ANSI_NULLS 设置值。无论随后何时执行存储过程，SET ANSI_NULLS 的设置都还原为其最初使用的值并生效。当在存储过程内唤醒调用 SET ANSI_NULLS 时，其设置不更改。
在执行分布式查询时应将 SET ANSI_NULLS 设置为 ON。
在计算列或索引视图上创建或操作索引时，SET ANSI_NULLS 也必须为 ON。如果 SET ANSI_NULLS 为 OFF，计算列或索引视图上带索引的表上的 CREATE、UPDATE、INSERT 和 DELETE 语句将失败。SQL Server 将返回一个错误，列出所有违反所需值的 SET 选项。另外，在执行 SELECT 语句时，如果 SET ANSI_NULLS 为 OFF，则 SQL Server 将忽略计算列或视图上的索引值并解析选择，就好象表或视图上没有这样的索引一样。
说明 ANSI_NULLS 是在处理计算列或索引视图上的索引时必须设置为所需值的七个 SET 选项之一。还必须将选项 ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、QUOTED_IDENTIFIER 和 CONCAT_NULL_YIELDS_NULL 设置为 ON，而必须将 NUMERIC_ROUNDABORT 设置为 OFF。
SQL Server ODBC 驱动程序和用于 SQL Server 的 Microsoft OLE DB 提供程序在连接时自动将 ANSI_NULLS 设置为 ON。该设置可以在 ODBC 数据源、ODBC 连接特性或 OLE DB 连接属性（它们在连接到 SQL Server 之前在应用程序中设置）中进行配置。对来自 DB-Library 应用程序的连接，SET ANSI_NULLS 默认为 OFF。
当 SET ANSI_DEFAULTS 为 ON 时，将启用 SET ANSI_NULLS。
SET ANSI_NULLS 的设置是在执行或运行时设置，而不是在分析时设置。
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的行。
说明 Microsoft® SQL Server™ 是将空字符串解释为单个空格还是真正的空字符串，取决于 sp_dbcmptlevel 的兼容级别设置。如果兼容级别小于或等于 65，SQL Server 就将空字符串解释为单个空格。如果兼容级别等于 70，则 SQL Server 将空字符串解释为空字符串。有关更多信息，请参见 sp_dbcmptlevel。
当 SET ANSI_NULLS 为 ON 时，所有对空值的比较均取值为 UNKNOWN。当 SET ANSI_NULLS 为 OFF 时，如果数据值是 NULL，则所有数据对空值的比较将取值为 TRUE。如果未指定，则应用当前数据库的 ANSI nulls 选项的设置。有关 ANSI nulls 数据库选项的更多信息，请参见 sp_dboption 和设置数据库选项。
为使脚本按预期运行，不管 ANSI nulls 数据库选项或 SET ANSI_NULLS 的设置是什么，在可能包含空值的比较中使用 IS NULL 和 IS NOT NULL。
对于存储过程，SQL Server 使用最初创建存储过程时的 SET ANSI_NULLS 设置值。无论随后何时执行存储过程，SET ANSI_NULLS 的设置都还原为其最初使用的值并生效。当在存储过程内唤醒调用 SET ANSI_NULLS 时，其设置不更改。
在执行分布式查询时应将 SET ANSI_NULLS 设置为 ON。
在计算列或索引视图上创建或操作索引时，SET ANSI_NULLS 也必须为 ON。如果 SET ANSI_NULLS 为 OFF，计算列或索引视图上带索引的表上的 CREATE、UPDATE、INSERT 和 DELETE 语句将失败。SQL Server 将返回一个错误，列出所有违反所需值的 SET 选项。另外，在执行 SELECT 语句时，如果 SET ANSI_NULLS 为 OFF，则 SQL Server 将忽略计算列或视图上的索引值并解析选择，就好象表或视图上没有这样的索引一样。
说明 ANSI_NULLS 是在处理计算列或索引视图上的索引时必须设置为所需值的七个 SET 选项之一。还必须将选项 ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、QUOTED_IDENTIFIER 和 CONCAT_NULL_YIELDS_NULL 设置为 ON，而必须将 NUMERIC_ROUNDABORT 设置为 OFF。
SQL Server ODBC 驱动程序和用于 SQL Server 的 Microsoft OLE DB 提供程序在连接时自动将 ANSI_NULLS 设置为 ON。该设置可以在 ODBC 数据源、ODBC 连接特性或 OLE DB 连接属性（它们在连接到 SQL Server 之前在应用程序中设置）中进行配置。对来自 DB-Library 应用程序的连接，SET ANSI_NULLS 默认为 OFF。
当 SET ANSI_DEFAULTS 为 ON 时，将启用 SET ANSI_NULLS。
SET ANSI_NULLS 的设置是在执行或运行时设置，而不是在分析时设置。
加载更多回复（2）
