SQL 中的 NULL 和编程语言中的 null 之间的区别 [英] Difference between NULL in SQL and null in programming languages

问题描述

我刚刚遇到了一个关于如何在 T-SQL(以及其他形式的 SQL)中处理 NULL 的有趣场景.这个问题和我很好地描述和回答了这个问题已经说明了下面的问题；

I've just come across an interesting scenario on how NULL is handled in T-SQL (and possibly other forms of SQL). The issue is pretty well described and answered by this question and I've illustrated the issue below;

-- SET ANSI_NULLS ON -- Toggle this between ON/OFF to see how it changes behaviour
DECLARE @VAR1 DATETIME 
DECLARE @VAR2 DATETIME 

SET @VAR1 = (SELECT CURRENT_TIMESTAMP)
SET @VAR2 = (SELECT NULL)

-- This will return 1 when ansi_nulls is off and nothing when ansi_nulls is on
SELECT 1 WHERE @VAR1 != @VAR2

DECLARE @TstTable TABLE (
   COL1 DATETIME,
   COL2 DATETIME)

INSERT INTO @TstTable
SELECT @VAR1, @VAR1
UNION 
SELECT @VAR1, NULL

-- This won't ever return a value irrespective of the ansi_nulls setting
SELECT * FROM @TstTable WHERE COL1 != COL2

这种情况让我质疑我对 SQL 中的空表示的理解.我一直认为 null 意味着它没有价值.鉴于 本页.它指出(我的重点……不过我可以很容易地突出显示整个段落)；

This situation led me to question my understanding of null representations specifically within SQL. I've always understood null to mean that it has no value. This seems to be an incorrect assumption given the first paragraph of this page. It states (my emphasis...I could quite easily just highlight the whole paragraph though);

NULL 值表示该值未知.NULL 的值是不同于空值或零值.没有两个空值是相等的.两个空值之间的比较，或一个 NULL 与任何其他值之间的比较值，返回未知，因为每个 NULL 的值都是未知的.

A value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

这也适用于 T-SQL 变量条件吗?它当然适用于我上面的 SELECT 1 WHERE @VAR1 != @VAR2 示例，但我不明白为什么在这种情况下 NULL 被认为是未知"而不是空/未初始化/无等.我知道 ANSI_NULLS 改变了它的工作方式，但它已被弃用，并将从未来的某个版本中删除.

Does this hold true for T-SQL variable conditions also? It certainly does for my SELECT 1 WHERE @VAR1 != @VAR2 example above, but I don't understand why NULL in this instance is considered "UNKNOWN" and not empty/uninitialised/nothing etc. I know ANSI_NULLS changes how this works, but it is deprecated and will be removed from some future version.

有人可以很好地解释为什么 T-SQL 中的 NULL 指的是 未知 值而不是 未初始化 值?如果是这样，您能否扩展您的答案以说明为什么具有 NULL 值的 T-SQL 变量也被认为是未知的?

Can someone offer a good explanation as to why NULL in T-SQL refers to an unknown value rather than an uninitialised value? If so, can you extend your answer to show why T-SQL variables with a NULL value are also considered to be unknown?

推荐答案

在 SQL 中，我们对在表中存储事实(也称为关系)感兴趣.

In SQL, we're interested in storing facts in tables (a.k.a relations).

Codd 要求是:

规则 3:系统处理空值:

Rule 3: Systematic treatment of null values:

DBMS 必须允许每个字段保持为空(或为空).具体来说，它必须支持缺失信息和不适用信息"的表示.这是系统的，不同于所有常规值(例如，不同于零或任何其他数字"，在数值的情况下)，并且独立于数据类型.这也暗示 DBMS 必须以系统的方式处理此类表示.

The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

我们最终得到的是三值逻辑(如@zmbq 所述).为什么会这样?

What we've ended up with is three-valued logic (as @zmbq stated). Why is it this way?

我们有两个项目要进行相等性比较.他们平等吗?好吧，事实证明我们(还)不知道第 1 项是什么，我们(还)不知道第 2 项是什么(两者都是 NULL).他们可能是平等的.他们可能是不平等的.用 TRUE 或 FALSE 回答相等比较同样是错误的.所以我们回答UNKNOWN.

We have two items that we're trying to compare for equality. Are they equal? Well, it turns out that we don't (yet) know what item 1 is, and we don't (yet) know what item 2 is (both are NULL). They might be equal. They might be unequal. It would be equally wrong to answer the equality comparison with either TRUE or FALSE. So we answer UNKNOWN.

在其他语言中，null 通常与指针一起使用(或在没有指针的语言中使用引用，但尤其不是 C++)，表示此时指针不指向任何东西.

In other languages, null is usually used with pointers (or references in languages without pointers, but notably not C++), to indicate that the pointer does not, at this time, point to anything.

这篇关于SQL 中的 NULL 和编程语言中的 null 之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！