为什么 NULL = NULL 在 SQL 服务器中评估为假 [英] Why does NULL = NULL evaluate to false in SQL server

问题描述

在 SQL Server 中，如果 where 子句中有 nullParam=NULL，它的计算结果总是为 false.这是违反直觉的，并给我带来了很多错误.我确实理解 IS NULL 和 IS NOT NULL 关键字是正确的方法.但是为什么 SQL Server 会这样呢?

In SQL server if you have nullParam=NULL in a where clause, it always evaluates to false. This is counterintuitive and has caused me many errors. I do understand the IS NULL and IS NOT NULL keywords are the correct way to do it. But why does SQL server behave this way?

推荐答案

在这种情况下将 null 视为未知"(或不存在").在这两种情况下，您都不能说它们相等，因为您不知道它们中的任何一个的价值.因此， null=null 评估为不为真(假或空，取决于您的系统)，因为您不知道表示它们相等的值.此行为在 ANSI SQL-92 标准中定义.

Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.

这取决于您的 ansi_nulls 设置.如果您关闭了 ANSI_NULLS，这将评估为真.运行以下代码作为示例...

This depends on your ansi_nulls setting. if you have ANSI_NULLS off, this WILL evaluate to true. Run the following code for an example...

set ansi_nulls off

if null = null
    print 'true'
else
    print 'false'


set ansi_nulls ON

if null = null
    print 'true'
else
    print 'false'

这篇关于为什么 NULL = NULL 在 SQL 服务器中评估为假的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！