不等于<>= NULL运算符

有人能解释一下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)
522477 次浏览
小开
最佳答案

<>是标准SQL-92;!=是它的等价物。两者都计算值，而NULL不是——NULL是一个占位符，表示没有值。

这就是为什么在这种情况下只能使用IS NULL/IS NOT NULL作为谓词的原因。

这种行为不是SQL Server特有的。所有符合标准的SQL方言都以相同的方式工作。

请注意:要比较你的值不为空值，你使用IS NOT NULL，而要比较非空值，你使用<> 'YOUR_VALUE'。我不能说我的值是否等于NULL，但我可以说我的值是否为NULL或not NULL。如果我的值不是NULL，我可以比较。

小开

NULL的唯一测试是is NULL或is NOT NULL。检验是否相等是毫无意义的，因为根据定义，人们不知道值是多少。

下面是维基百科上的一篇文章:

https://en.wikipedia.org/wiki/Null_(SQL)

小开

NULL没有值，因此不能使用标量值操作符进行比较。

换句话说，没有值可以等于(或不等于)NULL，因为NULL没有值。

因此，SQL有特殊的IS NULL和IS NOT NULL谓词来处理NULL。

小开

NULL不能使用比较操作符与任何值进行比较。NULL = NULL为false。Null不是一个值。IS运算符是专门为处理NULL比较而设计的。

小开

在SQL中，任何用NULL求值/计算的结果都是UNKNOWN

这就是为什么SELECT * FROM MyTable WHERE MyColumn != NULL或SELECT * FROM MyTable WHERE MyColumn <> NULL给出0个结果。

为了检查NULL值，提供了isNull函数。

此外，您可以像在第三个查询中使用的那样使用IS操作符。

小开

注意，这个行为是默认的(ANSI)行为。

如果你:

 SET ANSI_NULLS OFF

http://msdn.microsoft.com/en-us/library/ms188048.aspx

你会得到不同的结果。

SET ANSI_NULLS OFF显然将在未来消失…

小开

NULL不是任何东西…这是未知的。NULL不等于任何东西。这就是为什么在SQL查询中必须使用神奇的短语is NULL而不是= NULL

你可以引用这个:http://weblogs.sqlteam.com/markc/archive/2009/06/08/60929.aspx

小开

我想建议我做的这个代码，如果有一个值的变化， i是新值，d是旧值(尽管顺序无关紧要)。就这一点而言，从值到null的变化是变化，反之亦然，但从null到null不是变化(当然，从值到另一个值是变化，但从值到相同的值不是) 

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

要使用此函数，可以

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

结果如下:

---- 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

使用sql_variant可以兼容各种类型

小开

我们使用

SELECT * FROM MyTable WHERE ISNULL(MyColumn, ' ') = ' ';

返回MyColumn为NULL的所有行或MyColumn为空字符串的所有行。对于许多“最终用户”来说，NULL和空字符串问题是一个没有需要和混淆点的区别。

小开

null表示没有值或未知值。它没有指定为什么没有值，这可能会导致一些歧义。

假设你像这样运行一个查询:

SELECT *
FROM orders
WHERE delivered=ordered;

也就是说，你正在寻找ordered和delivered日期相同的行。

当其中一列或两列都为空时会发生什么?

因为至少有一个日期是未知的，你不能指望说这两个日期是相同的。当这两个日期未知时也是如此:如果我们甚至不知道它们是什么，它们怎么可能是相同的呢?

因此，任何将null作为值处理的表达式都必须失败。在本例中，它将不匹配。如果你尝试以下方法，也会有同样的效果:

SELECT *
FROM orders
WHERE delivered<>ordered;

同样，如果我们不知道两个值不是什么，我们怎么能说它们是相同的呢?

SQL对缺失值有一个特定的测试:

IS NULL

具体来说，它不是比较值，而是寻找失踪值。

最后，关于!=操作符，据我所知，它实际上不在任何标准中，但它得到了广泛的支持。它的加入是为了让来自某些语言的程序员感觉更自在。坦率地说，如果一个程序员很难记住他们正在使用的语言，那么他们就没有一个好的开始。


提交答案