SQL NULL 上的不等于 <> != 运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5658457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Not equal <> != operator on NULL
提问by Maxim Gershkovich
Could someone please explain the following behavior in SQL?
有人可以解释一下 SQL 中的以下行为吗？
SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)
回答by OMG Ponies
<>is Standard SQL-92; !=is its equivalent. Both evaluate for values, which NULLis not -- NULLis a placeholder to say there is the absence of a value.
<>是标准 SQL-92；!=是它的等价物。两者都评估值，而NULL不是 -NULL是一个占位符，表示没有值。
Which is why you can only use IS NULL/IS NOT NULLas predicates for such situations.
这就是为什么你只能使用IS NULL/IS NOT NULL作为这种情况的谓词。
This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.
此行为并非特定于 SQL Server。所有符合标准的 SQL 方言都以相同的方式工作。
Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not nullvalue, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.
注意：要比较您的值是否为 null，请使用IS NOT NULL，而要与非 null值进行比较，请使用<> 'YOUR_VALUE'. 我不能说我的值是否等于或不等于 NULL，但我可以说我的值是 NULL 还是 NOT NULL。我可以比较我的值是否不是 NULL。
回答by Barry Brown
NULL has no value, and so cannot be compared using the scalar value operators.
NULL 没有值，因此无法使用标量值运算符进行比较。
In other words, no value can ever be equal to (or not equal to) NULL because NULL has no value.
换句话说，任何值都不可能等于（或不等于）NULL，因为 NULL 没有值。
Hence, SQL has special IS NULL and IS NOT NULL predicates for dealing with NULL.
因此，SQL 有特殊的 IS NULL 和 IS NOT NULL 谓词来处理 NULL。
回答by Cade Roux
Note that this behavior is the default (ANSI) behavior.
请注意，此行为是默认 (ANSI) 行为。
If you:
如果你：
SET ANSI_NULLS OFF
http://msdn.microsoft.com/en-us/library/ms188048.aspx
http://msdn.microsoft.com/en-us/library/ms188048.aspx
You'll get different results.
你会得到不同的结果。
SET ANSI_NULLS OFFwill apparently be going away in the future...
SET ANSI_NULLS OFF显然将来会消失......
回答by Mahendra Liya
In SQL, anything you evaluate / compute with NULLresults into UNKNOWN
在 SQL 中，任何你评估/计算的NULL结果都变成了 UNKNOWN
This is why SELECT * FROM MyTable WHERE MyColumn != NULLor SELECT * FROM MyTable WHERE MyColumn <> NULLgives you 0 results.
这就是为什么SELECT * FROM MyTable WHERE MyColumn != NULLorSELECT * FROM MyTable WHERE MyColumn <> NULL给你 0 结果。
To provide a check for NULLvalues, isNull function is provided.
为了检查NULL值，提供了isNull 函数。
Moreover, you can use the ISoperator as you used in the third query.
此外，您可以使用IS在第三个查询中使用的运算符。
Hope this helps.
希望这可以帮助。
回答by dkretz
The only test for NULL is IS NULL or IS NOT NULL. Testing for equality is nonsensical because by definition one doesn't know what the value is.
NULL 的唯一测试是 IS NULL 或 IS NOT NULL。测试相等性是无意义的，因为根据定义，人们不知道值是什么。
Here is a wikipedia article to read:
这里有一篇维基百科文章可供阅读：
回答by Jeff Mergler
We use
我们用
SELECT * FROM MyTable WHERE ISNULL(MyColumn, ' ') = ' ';
to return all rows where MyColumn is NULL or all rows where MyColumn is an empty string. To many an "end user", the NULL vs. empty string issue is a distinction without a need and point of confusion.
返回 MyColumn 为 NULL 的所有行或 MyColumn 为空字符串的所有行。对于许多“最终用户”来说，NULL 与空字符串的问题是一种区分，没有必要和混淆点。
回答by Hrvoje Batrnek
I just don't see the functional and seamless reason for nulls not to be comparable to other values or other nulls, cause we can clearly compare it and say they are the same or not in our context. It's funny. Just because of some logical conclusions and consistency we need to bother constantly with it. It's not functional, make it more functional and leave it to philosophers and scientists to conclude if it's consistent or not and does it hold "universal logic". :) Someone may say that it's because of indexes or something else, I doubt that those things couldn't be made to support nulls same as values. It's same as comparing two empty glasses, one is vine glass and other is beer glass, we are not comparing the types of objects but values they contain, same as you could compare int and varchar, with null it's even easier, it's nothing and what two nothingness have in common, they are the same, clearly comparable by me and by everyone else that write sql, because we are constantly breaking that logic by comparing them in weird ways because of some ANSI standards. Why not use computer power to do it for us and I doubt it would slow things down if everything related is constructed with that in mind. "It's not null it's nothing", it's not apple it's apfel, come on... Functionally is your friend and there is also logic here. In the end only thing that matter is functionality and does using nulls in that way brings more or less functionality and ease of use. Is it more useful?
我只是没有看到 null 不能与其他值或其他 null 进行比较的功能和无缝原因，因为我们可以清楚地比较它并说它们在我们的上下文中是否相同。这很有趣。仅仅因为一些合乎逻辑的结论和一致性，我们需要不断地为它烦恼。它不是功能性的，让它更具功能性，让哲学家和科学家来判断它是否一致以及它是否具有“通用逻辑”。:) 有人可能会说这是因为索引或其他原因，我怀疑这些东西不能支持与值相同的空值。这与比较两个空玻璃杯相同，一个是藤蔓玻璃，另一个是啤酒玻璃，我们不是比较对象的类型，而是比较它们包含的值，就像您可以比较 int 和 varchar 一样，用 null it' 甚至更容易，它没有什么和两个虚无有什么共同点，它们是相同的，我和其他编写 sql 的人显然可以比较，因为由于某些 ANSI 标准，我们通过以奇怪的方式比较它们不断打破这种逻辑。为什么不使用计算机能力来为我们做这件事，我怀疑如果所有相关的事情都考虑到这一点，它会减慢速度。“它不是空它什么都不是”，它不是苹果它是 apfel，来吧......功能上是你的朋友，这里也有逻辑。最后，唯一重要的是功能，并且以这种方式使用空值确实会带来更多或更少的功能和易用性。它更有用吗？因为由于某些 ANSI 标准，我们通过以奇怪的方式比较它们不断打破这种逻辑。为什么不使用计算机能力来为我们做这件事，我怀疑如果所有相关的东西都考虑到这一点，它会减慢速度。“它不是空它什么都不是”，它不是苹果它是 apfel，来吧......功能上是你的朋友，这里也有逻辑。最后，唯一重要的是功能，并且以这种方式使用空值确实会带来更多或更少的功能和易用性。它更有用吗？因为由于某些 ANSI 标准，我们通过以奇怪的方式比较它们不断打破这种逻辑。为什么不使用计算机能力来为我们做这件事，我怀疑如果所有相关的东西都考虑到这一点，它会减慢速度。“它不是空它什么都不是”，它不是苹果它是 apfel，来吧......功能上是你的朋友，这里也有逻辑。最后，唯一重要的是功能，并且以这种方式使用空值确实会带来更多或更少的功能和易用性。它更有用吗？不是苹果，它是 apfel，拜托...功能上是你的朋友，这里也有逻辑。最后，唯一重要的是功能，并且以这种方式使用空值确实会带来更多或更少的功能和易用性。它更有用吗？不是苹果，它是 apfel，拜托...功能上是你的朋友，这里也有逻辑。最后，唯一重要的是功能，并且以这种方式使用空值确实会带来更多或更少的功能和易用性。它更有用吗？
Consider this code:
考虑这个代码：
SELECT CASE WHEN NOT (1 = null or (1 is null and null is null)) THEN 1 ELSE 0 end
How many of you knows what will this code return? With or without NOT it returns 0. To me that is not functional and it's confusing. In c# it's all as it should be, comparison operations return value, logically this too produces value, because if it didn't there is nothing to compare (except. nothing :) ). They just "said": anything compared to null "returns" 0 and that creates many workarounds and headaches.
你们中有多少人知道这段代码会返回什么？有或没有 NOT 它返回 0。对我来说，这不是功能性的，而且令人困惑。在 c# 中，一切都应该如此，比较操作返回值，从逻辑上讲，这也会产生值，因为如果没有，则没有什么可比较的（除了。没有 :)）。他们只是“说”：与 null 相比，任何东西都会“返回”0，这会产生许多变通方法和令人头疼的问题。
This is the code that brought me here:
这是将我带到这里的代码：
where a != b OR (a is null and b IS not null) OR (a IS not null and b IS null)
I just need to compare if two fields (in where) have different values, I could use function, but...
我只需要比较两个字段（在那里）是否具有不同的值，我可以使用函数，但是......
回答by Vincent Ramdhanie
NULL Cannot be compared to any value using the comparison operators. NULL = NULL is false. Null is not a value. The IS operator is specially designed to handle NULL comparisons.
NULL 不能与使用比较运算符的任何值进行比较。NULL = NULL 为假。Null 不是一个值。IS 运算符专门设计用于处理 NULL 比较。
回答by Manngo
Old question, but the following might offer some more detail.
老问题，但以下内容可能会提供更多细节。
nullrepresents no value or an unknown value. It doesn't specify whythere is no value, which can lead to some ambiguity.
null表示没有值或未知值。它没有说明为什么没有值，这可能会导致一些歧义。
Suppose you run a query like this:
假设您运行这样的查询：
SELECT *
FROM orders
WHERE delivered=ordered;
that is, you are looking for rows where the orderedand delivereddates are the same.
也就是说，您正在寻找ordered和delivered日期相同的行。
What is to be expected when one or both columns are null?
当一列或两列为空时会发生什么？
Because at least one of the dates is unknown, you cannot expect to say that the 2 dates are the same. This is also the case when bothdates are unknown: how can they be the same if we don't even know what they are?
因为至少有一个日期是未知的，所以您不能指望说这两个日期是相同的。这也是两个日期未知的情况：如果我们甚至不知道它们是什么，它们怎么可能相同？
For this reason, any expression treating nullas a value must fail. In this case, it will not match. This is also the case if you try the following:
因此，任何null视为值的表达式都必须失败。在这种情况下，它将不匹配。如果您尝试以下操作，情况也是如此：
SELECT *
FROM orders
WHERE delivered<>ordered;
Again, how can we say that two values are notthe same if we don't know what they are.
同样，我们怎么能说两个值是不相同的，如果我们不知道它们是什么。
SQL has a specific test for missing values:
SQL 对缺失值有一个特定的测试：
IS NULL
Specifically it is not comparingvalues, but rather it seeks out missingvalues.
具体来说，它不是比较值，而是寻找缺失值。
Finally, as regards the !=operator, as far as I am aware, it is not actually in any of the standards, but it is very widely supported. It was added to make programmers from some languages feel more at home. Frankly, if a programmer has difficulty remembering what language they're using, they're off to a bad start.
最后，关于!=运营商，据我所知，它实际上不在任何标准中，但它得到了非常广泛的支持。添加它是为了让某些语言的程序员感觉更自在。坦率地说，如果程序员难以记住他们使用的是什么语言，那么他们的开局就很糟糕。
回答by Yariv de Botton
I would like to suggest this code I made to find if there is a change in a value,
ibeing the new value and dbeing the old (although the order does not matter). For that matter, a change from value to null or vice versa is a change but from null to null is not (of course, from value to another value is a change but from value to the same it is not).
我想建议我使用这段代码来查找值是否发生变化，
i即新值和d旧值（尽管顺序无关紧要）。就此而言，从 value 到 null 的更改或反之亦然是更改，但从 null 到 null 不是（当然，从 value 到另一个 value 是更改，但从 value 到相同的不是）。
CREATE FUNCTION [dbo].[ufn_equal_with_nulls]
(
@i sql_variant,
@d sql_variant
)
RETURNS bit
AS
BEGIN
DECLARE @in bit = 0, @dn bit = 0
if @i is null set @in = 1
if @d is null set @dn = 1
if @in <> @dn
return 0
if @in = 1 and @dn = 1
return 1
if @in = 0 and @dn = 0 and @i = @d
return 1
return 0
END
To use this function, you can
要使用此功能，您可以
declare @tmp table (a int, b int)
insert into @tmp values
(1,1),
(1,2),
(1,null),
(null,1),
(null,null)
---- in select ----
select *, [dbo].[ufn_equal_with_nulls](a,b) as [=] from @tmp
---- where equal ----
select *,'equal' as [Predicate] from @tmp where [dbo].[ufn_equal_with_nulls](a,b) = 1
---- where not equal ----
select *,'not equal' as [Predicate] from @tmp where [dbo].[ufn_equal_with_nulls](a,b) = 0
The results are:
结果是：
---- in select ----
a b =
1 1 1
1 2 0
1 NULL 0
NULL 1 0
NULL NULL 1
---- where equal ----
1 1 equal
NULL NULL equal
---- where not equal ----
1 2 not equal
1 NULL not equal
NULL 1 not equal
The usage of sql_variant makes it compatible for variety of types
sql_variant 的使用使得它兼容多种类型
