为什么 NULL = NULL 在 SQL Server 中评估为 false [英] Why does NULL = NULL evaluate to false in 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?

解决方案

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.

EDIT: 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 Server 中评估为 false的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！