ANSI_NULLS 在 TSQL 中如何工作? [英] How does ANSI_NULLS work in TSQL?

问题描述

SET ANSI_NULLS OFF 似乎在 TSQL 中给出不同的结果，具体取决于您是比较表中的字段还是值.谁能帮我理解为什么我的最后两个查询没有结果?我不是在寻找解决方案，只是在寻找解释.

SET ANSI_NULLS OFF seems to give different results in TSQL depending on whether you're comparing a field from a table or a value. Can anyone help me understand why the last 2 of my queries give no results? I'm not looking for a solution, just an explanation.

select 1 as 'Col' into #a
select NULL as 'Col' into #b

--This query gives results, as expected.  
SET ANSI_NULLS OFF
select * from #b
where NULL = Col

--This query gives results, as expected.
SET ANSI_NULLS OFF
select * from #a
where NULL != Col

--This workaround gives results, too.
select * from #a a, #b b
where isnull(a.Col, '') != isnull(b.Col, '')

--This query gives no results, why?
SET ANSI_NULLS OFF
select * from #a a, #b b
where a.Col != b.Col

--This query gives no results, why?
SET ANSI_NULLS OFF
select * from #a a, #b b
where b.Col != a.Col

推荐答案

最后两个查询失败的原因是 SET ANSI_NULLS ON/OFF 仅适用于与变量或 NULL 进行比较时价值.当您比较列值时，它不适用.来自 BOL:

The reason the last two queries fail is that SET ANSI_NULLS ON/OFF only applies when you are comparing against a variable or the NULL value. It does not apply when you are comparing column values. From the BOL:

SET ANSI_NULLS ON 影响比较只有当操作数之一比较要么是一个变量是 NULL 或文字 NULL.如果两者比较的边是列或复合表达式，设置确实不影响比较.

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

这篇关于ANSI_NULLS 在 TSQL 中如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！