“<>"与“不在" [英] "<>" vs "NOT IN"
问题描述
前几天我在调试一个存储过程，发现了一些类似这样的逻辑:
I was debugging a stored procedure the other day and found some logic something like this:
SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)
这没有返回任何内容.我认为<>"看起来有点奇怪，所以我将其更改为NOT IN"，然后一切正常.我想知道这是为什么?这是一个相当古老的过程，我不确定这个问题已经存在了多久，但我们最近在发现此问题时从 SQL Server 2005 切换到 SQL Server 2008.<>"和NOT IN"之间的真正区别是什么?Server2005 和 2008 之间的行为是否发生了变化?
This returned nothing. I thought it looked a little odd with the "<>" so I changed it to "NOT IN" and then everything worked fine. I was wondering why this is? This is a pretty old proc and I am not really sure how long the issue has been around, but we recently switched from SQL Server 2005 to SQL Server 2008 when this was discovered. What is the real difference between "<>" and "NOT IN" and has the behavior changed between Server2005 and 2008?
推荐答案
SELECT something
FROM someTable
WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)
检查列表中的任何值.
然而，NOT IN 不是 NULL 容忍的.如果子查询返回一组包含 NULL 的值，则根本不会返回任何记录.(这是因为在内部 NOT IN 被优化为 idcode <> 'foo' AND idcode <> 'bar' AND idcode <> NULL 等，这将始终失败，因为任何与 NULL 的比较都会产生 UNKNOWN，从而阻止整个表达式变为 TRUE.)
However, the NOT IN is not NULL-tolerant. If the sub-query returned a set of values that contained NULL, no records would be returned at all. (This is because internally the NOT IN is optimized to idcode <> 'foo' AND idcode <> 'bar' AND idcode <> NULL etc., which will always fail because any comparison to NULL yields UNKNOWN, preventing the whole expression from ever becoming TRUE.)
一个更好的、可以容忍 NULL 的变体是这样的:
A nicer, NULL-tolerant variant would be this:
SELECT something
FROM someTable
WHERE NOT EXISTS (SELECT ids FROM tmpIdTable WHERE ids = someTable.idcode)
<小时>
我最初认为:
I initially assumed that this:
SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)
只检查第一个值.事实证明，这个假设至少对于 SQL Server 来说是错误的，它实际上触发了他的错误:
would check against the first value only. It turns out that this assumption is wrong at least for SQL Server, where it actually triggers his error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
这篇关于“<>"与“不在"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
