oracle 为什么在 SQL 中 NULL 不能与 NULL 匹配？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12853944/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why in SQL NULL can't match with NULL?
提问by Joe Norman
I'm new to SQL concepts, while studying NULL expression I wonder why NULL can't match with NULL can anyone tell me a real world example to simply this concept?
我是 SQL 概念的新手，在研究 NULL 表达式时，我想知道为什么 NULL 不能与 NULL 匹配，谁能告诉我一个真实世界的例子来简化这个概念？
回答by mr_eclair
Rule : Not even a NULL can be equal to NULL.
规则：即使是 NULL 也不能等于 NULL。
A Non-Technical aspect
非技术方面
If you ask two girls, how old they are? may be you would hear them to refuse to answer your question, Both girls are giving you NULL as age and this doesn't mean both have similar age. So there is nothing can be equal to null.
如果你问两个女孩，她们几岁？可能你会听到他们拒绝回答你的问题，两个女孩都给你 NULL 作为年龄，这并不意味着两个女孩的年龄相似。所以没有什么可以等于null。
回答by Damien_The_Unbeliever
NULLindicates an absenceof a value. The designers of SQL decided that it made sense that, when asked whether A(for which we do not know its value) and B(for which we do not know its value) are equal, the answer must be UNKNOWN- they might be equal, they might not be. We do not have adequate information to decide either way.
NULL表示没有值。SQL 的设计者认为，当被问及A（我们不知道它的值）和B（我们不知道它的值）是否相等时，答案必须是UNKNOWN——它们可能相等，它们可能相等。不是。我们没有足够的信息来决定任何一种方式。
You might want to read up on Three valued logic- the possible results of any comparison in SQL are TRUE, FALSEand UNKNOWN(mysql treats UNKNOWNand NULLas synonymous. Not all RDBMSs do)
您可能想阅读三值逻辑- SQL 中任何比较的可能结果是TRUE, FALSEand UNKNOWN（mysql 将UNKNOWN和NULL视为同义词。并非所有 RDBMS 都这样做）
回答by Prescott
NULLis an unknown value. Therefore it makes little sense to judge NULL == NULL. That's like asking "is this unknown value equal to that unknown value" - no clue..
NULL是一个未知值。因此判断意义不大NULL == NULL。这就像问“这个未知值是否等于那个未知值” - 不知道..
See why is null not equal to null falsefor a possibly better explaination
了解为什么 null 不等于 null false以获得更好的解释
回答by Khaleel
回答by raider33
If you need to make a comparison where NULL does indeed equal NULL, you can use a pair of coalescemethods with a specialdefault value. The easiest example is on a nullable string column.
如果您需要在 NULL 确实等于 NULL 的情况下进行比较，您可以使用一对coalesce具有特殊默认值的方法。最简单的示例是可空字符串列。
coalesce(MiddleName,'')=coalesce(@MiddleName,'')
This comparison returns true if @MiddleName variable has a null value and the MiddleName column also has a null value. Of course, it also matches empty strings too. If that is an issue, you could change the special value to something silly, like
如果@MiddleName 变量具有空值并且 MiddleName 列也具有空值，则此比较返回 true。当然，它也匹配空字符串。如果这是一个问题，您可以将特殊值更改为愚蠢的值，例如
coalesce(MiddleName,'<NULL_DEFAULT>')=coalesce(@MiddleName,'<NULL_DEFAULT>')
回答by Shabarinath Volam
You cannot use =for NULL instead you can use IS NULL
您不能将=用于 NULL，而可以使用IS NULL
回答by gks
回答by Stephen Connolly
In SQL the WHERE clause only includes the value if the result of the expression is equal to TRUE. (This is not the same as "if the result of the expression is not FALSE")
在 SQL 中，WHERE 子句仅包含表达式结果等于 TRUE 时的值。（这与“如果表达式的结果不为 FALSE”不同）
Any binary operation in SQL that has NULL on one side evaluates to NULL (think of NULL as being a synonym for unknown)
SQL 中任何一侧有 NULL 的二元运算都会计算为 NULL（将 NULL 视为未知的同义词）
so
所以
Select ... Where null = null
Select ... Where field = null
Select ... Where null = field
Will all return no rows as in each case the where class evaluates to NULL which is not TRUE
都将不返回任何行，因为在每种情况下 where 类的计算结果为 NULL，这不是 TRUE
回答by bimal
Actually NULL means UNKNOWN value So how we compare two UNKNOWN values.
实际上 NULL 意味着 UNKNOWN 值 那么我们如何比较两个 UNKNOWN 值。
