ANSI_NULLS 在 TSQL 中如何工作？
SET ANSI_NULLS OFF 似乎在 TSQL 中给出不同的结果，具体取决于您是比较表中的字段还是值。谁能帮助我理解为什么我的最后两个查询没有结果？我不是在寻找解决方案，只是寻求解释。
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 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
最后两个查询失败的原因是
SET ANSI_NULLS ON/OFF仅在您与变量或 NULL 值进行比较时适用。当您比较列值时它不适用。来自玻利维亚：The reason the last two queries fail is that
SET ANSI_NULLS ON/OFFonly 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:任何与空值相比都会失败。即使比较两个空值也会失败。由于（恕我直言）对 NULL 的愚蠢处理，甚至 != 也会失败。
也就是说， != 查询可以重写为：
最后一个查询与倒数第二个查询相同，因为比较的顺序并不重要。
顺便说一句，您的解决方法之所以有效，只是因为您正在测试 #b.Col 列中的空值并将其显式转换为 ''，然后允许您的查询在它们之间进行字符串比较。另一种写作方式是：
Anything compared to a null value fails. Even comparing two null values will fail. Even the != will fail because of the (IMHO) stupid handling of NULL.
That said, the != queries could be rewritten to say:
The last query is identical to the second to last query as the order of the comparison doesn't matter.
Incidentally, your workaround works simply because you are testing for a null value in the #b.Col column and explicitly converting it to a '' which then allows your query to do a string compare between them. An alternative way of writing that would be: