在空值上使用不等于 <> != 运算符

Question

请问能否解释一下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)

- Maxim Gershkovich

Answer 1

<> 是标准 SQL-92 中的符号，而它的等价符号是 !=。两者都用于对值进行比较，而 NULL 不参与比较，NULL 只是一个占位符，表示缺少值。

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

这个行为不仅适用于 SQL Server，所有符合标准的 SQL 方言都是同样的方式工作。

注意：如果要比较值不为空，则使用 IS NOT NULL，如果要比较非空值，则使用 <> 'YOUR_VALUE'。我不能说我的值等于或不等于 NULL，但我可以说我的值是 NULL 还是 NOT NULL。我可以比较我的值是否与 NULL 不同。

- OMG Ponies

---

实际上，我相信在92规范中是使用<>，但大多数供应商支持使用!=，或者它包含在更新的规范中，比如99或03。 - Thomas

@Thomas：据我所知，Oracle在 ~9i 之前不支持 !=，这带来了很多 ANSI-92 语法。我的看法是MySQL也类似，在4.x版本开始支持。 - OMG Ponies

这似乎表明！=可能已经作为<>的替代品在后来的规范中被包含进去了。我手头没有新规范，所以无法确定。 - Thomas

WHERE MyColumn != NULL 或 WHERE MyColumn = NULL 的结果是确定的吗？换言之，是否保证无论数据库中的 MyColumn 是否可为空，它都将始终返回 0 行？ - Slauma

需要注意的是，由于!=只检查值，像这样使用WHERE MyColumn != 'somevalue'并不会返回NULL记录。 - jsumrall

Answer 2

NULL没有值，因此无法使用标量值运算符进行比较。

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

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

- Barry Brown

---

与OP的说法相反，这不是“Microsoft SQL”。三值逻辑在SQL标准中有定义，在此方面，微软遵循了该标准。 - TomTom

我并不是在说这只是微软的行为。我只是在说明我观察到它在 Microsoft SQL Server 上发生过。 - Maxim Gershkovich

感兴趣的话，这种预期行为有没有任何有用的情况？对我来说，'a' != null 不返回值(true/1)似乎不符合直觉，并且有时会让我感到困惑！我本以为“某个值与无值进行比较”总是“不相等”，但也许这只是我自己的看法？！? - DarthPablo

我认为有趣的是人们将NULL描述为“没有值”。类似地，就像说数字1“有一个值”一样，实际上它本身就是一个值。但是NULL代表着非值。 - systemaddict

作为手动解决方法，您可以通常使用SELECT * FROM MyTable WHERE coalesce(MyColumn, 'x') <> 'x'来分配一个常量，如果它是NULL值，只要为哨兵值x（在这种情况下为字符串/字符）提供适当的数据类型。这是TSQL语法，但Oracle和其他引擎也有类似的功能。 - systemaddict

这里有一个有趣的小测验，可以检查您是否理解了它的工作原理：https://blog.drnielsen.de/php/laravel/how-null-works - Adam

Answer 3

请注意，这是默认的（ANSI）行为。

如果您：

 SET ANSI_NULLS OFF

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

执行结果将会有所不同。

SET ANSI_NULLS OFF 显然将在未来被废弃......

- Cade Roux

---

+1... 不够快。现在我什么时候才能在索引中获得“重复”的NULL值？ :( - user166390

在 SQL Server 索引中添加 WHERE 子句（例如 create unique index UK_MyTable on MyTable (Column) where Column is not null）可以通过筛选索引获得重复的 NULL ：http://msdn.microsoft.com/en-us/library/cc280372.aspx - Anthony Mills

来自文档的注释：当 SET ANSI_NULLS 为 OFF 时，等于（=）和不等于（<>）比较运算符不遵循ISO标准。使用 WHERE column_name = NULL 的 SELECT 语句返回具有 column_name 中空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回该列中非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有既不是 XYZ_value 也不是 NULL 的行。在我看来，这最后一个语句似乎在结果中排除了 NULL 值有点奇怪！ - DarthPablo

来自msdn文档的重要说明： 在SQL Server的将来版本（大于2014），ANSI_NULLS将始终为ON，任何显式将该选项设置为OFF的应用程序都将生成错误。避免在新开发工作中使用此功能，并计划修改当前使用此功能的应用程序。 - Otiel

Answer 4

我们使用。

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

返回所有MyColumn为NULL或为空字符串的行。对于大多数“终端用户”来说，NULL与空字符串之间的区别是毫无必要的，并且会引起混淆。

- Jeff Mergler

---

这是最好的解决方法，只需注意在少数情况下空字符串与null比较并不无意义。 - sisisisi

Answer 5

在 SQL 中，任何使用 NULL 进行计算或评估的结果都是未知的。

因此，SELECT * FROM MyTable WHERE MyColumn != NULL 或 SELECT * FROM MyTable WHERE MyColumn <> NULL 会返回 0 条结果。

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

此外，您可以像第三个查询中所使用的那样使用 IS 运算符。

- Mahendra Liya

---

在SQL中，任何你使用NULL进行计算的结果都会变成“NULL”--这是不正确的。你所指的结果应该是UNKNOWN。 - onedaywhen

@MahendraLiya，isNull函数不提供检查NULL值的功能，但是它可以"用指定的替换值替换NULL"。你应该使用 IS NULL 或 IS NOT NULL，而不是ISNULL函数，因为它是一个不同的东西。 - Reversed Engineer

Answer 6

仅能用IS NULL或IS NOT NULL来测试NULL。测试相等是无意义的，因为按定义一个不知道该值是什么。

以下是维基百科文章: https://en.wikipedia.org/wiki/Null_(SQL)

- dkretz

Answer 7

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

假设您运行以下查询：

SELECT *
FROM orders
WHERE delivered=ordered;

也就是说，您正在查找其中ordered和delivered日期相同的行。

当一个或两个列为空时会发生什么？

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

因此，将null视为值的任何表达式都必须失败。 在这种情况下，它将不匹配。 如果尝试以下操作也是如此：

SELECT *
FROM orders
WHERE delivered<>ordered;

再次，如果我们不知道两个值是什么，我们怎么能说它们不相同。

SQL有一个特定的测试用于缺失值：

IS NULL

具体来说，它不是在比较值，而是寻找缺少的值。

最后，关于"!="运算符，据我所知，它实际上并没有被任何标准所采用，但它得到了广泛的支持。它的添加是为了让一些语言的程序员感觉更加亲切。老实说，如果一个程序员难以记住他们正在使用的编程语言，那么他们就已经开始走弯路了。

- Manngo

---

这是@Hove在他的答案中描述的同样“无意义”的“逻辑”。事实上，在这个情况下，没有必要添加额外的废话；可以很容易地假设当我们将某个东西与NULL进行比较时，我们的意思是将一个值与“具有NULL值”的值进行比较，而不是将值与底层NULL正在¿拥有？但我们不知道的“未确定的值”进行比较，显然我们永远也无法知道。那真的会让事情变得容易些。 - Pere

@Pere我不会说这是严格“荒谬的”，而且我不确定写IS NULL是否比写= NULL更繁琐。我认为如果 WHERE columnA = columnB 与 WHERE columnA = NULL 有相同的解释，那么它会更一致，而不是将后者视为特殊情况。请记住， NULL _不是_一个值。在编程语言中，如果合法测试variable == null，则是因为null具有不同的含义；它并不代表某些未知的东西，而是有意重置值。 SQL则不然。 - Manngo

这就是为什么我把它放在引号之间，@Mangoo ;) （还有“逻辑”）。不要生我的气；我说的是 ANSI 的“推理”，而不是你的解释。我同意在你最新的例子中，“IS NULL”和“=NULL”之间没有额外开销。但看看Hover的最后一个例子。我已经厌倦了一遍又一遍地经历这种情况，不得不做大量的多余检查... - Pere

Answer 8

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

- Vincent Ramdhanie

---

有时在一些临时查询中，我会使用null=null代替1=0，这样做常常让人感到困惑，我喜欢这样。如果有人抱怨，我就会改成null != null :)请注意，我已经尽力使翻译保持忠实于原文，同时更易于理解。 - SWeko

"NULL = NULL is false"这并不正确。NULL = NULL的结果是未知，而不是false。 - nvogel

@dportas，是这样，但我的意思是在条件语句中它不会被评估为真。 - Vincent Ramdhanie

@VincentRamdhanie 不是false；事实上，在Postgres中，它将被评估为NULL。 - Pere

Answer 9

我想建议使用我编写的代码来查找值是否发生变化，i 为新值，d 为旧值（顺序不重要）。对于这个问题，从一个值到 null 或者从 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 可以使其兼容多种类型。

- Yariv de Botton

Answer 10

NULL并不代表任何事情……它是未知的。NULL不等于任何东西。这就是为什么在SQL查询中，你必须使用魔法短语IS NULL而不是= NULL。

你可以参考这个链接：http://weblogs.sqlteam.com/markc/archive/2009/06/08/60929.aspx

- Shadow