SQL - != 'NULL' 的解释 [英] SQL - An explanation of != 'NULL'

问题描述

我的SSMS代码如下:

My SSMS code is as follows:

Select top(50)
From FilteredContact
Where statuscode = 1 
and emailaddress1 != 'NULL' 
and telephone1 != 'NULL' 
and address1_line1 != 'NULL' 

我意识到理想情况下，最后三行的格式如下:

I realize that ideally, the last three lines are to be formatted as follows:

and emailaddress1 IS NOT NULL
and telephone1 IS NOT NULL
and address1_line1 IS NOT NULL

然而，我的原始代码实际上运行良好.这是为什么?我认为 NULL 是一个未知值，而不是等于 'NULL' 的字符串值

However, my original code actually worked completely fine. Why is that? I thought NULL was an unknown value, not a string value equal to 'NULL'

如果有人知道，将不胜感激！谢谢.

If anyone knows, an explanation would be greatly appreciated! Thanks.

推荐答案

然而，我的原始代码实际上运行良好.

However, my original code actually worked completely fine.

偶然工作.例如，如果 emailaddress1 ='NULL'(一个由四个字符组成的字符串值:N U L L)，则条件评估为 FALSE，并且不正确.由于电子邮件地址不太可能是 NULL，所以是的 - 它工作得很好:)

Worked by accident. If for example emailaddress1 ='NULL' (a string value consisting of four characters: N U L L), then the condition evaluates to FALSE, and is incorrect. Since the email address is unlikely to be NULL, then yes - it worked perfectly :)

请参阅此答案以获得详细说明:NOT IN selection with NULL值

简而言之:

See this answer for a long explanation: NOT IN selection with NULL values

In short:

与 NULL 和三值逻辑 (3VL) 的比较

由于 Null 不是任何数据域的成员，因此它不被视为值"，而是一个表示不存在的标记(或占位符)有价值的.因此，与 Null 的比较永远不会导致True 或 False，但总是在第三个逻辑结果中，未知. [8]下面表达式的逻辑结果，比较Null 的值 10 是未知的:

Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.[8] The logical result of the expression below, which compares the value 10 to Null, is Unknown:

SELECT 10 = NULL -- 结果未知

SELECT 10 = NULL -- Results in Unknown

这样两个比较:x = NULL 并且 x <> NULL 计算为 NULL(未知).

在你的条件中，比如 and phone1 != 'NULL'，如果 telephone 为 NULL，那么整个条件的计算结果为 NULL，这被视为 WHERE 子句中的 false 条件.

In your condition, say and telephone1 != 'NULL', if telephone is NULL, then the whole conditions evaluates to NULL, which is treated as false condition in the WHERE clause.

这篇关于SQL - != 'NULL' 的解释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！