如果发生 NULL，则 Where 子句拒绝行 [英] Where Clause Rejecting Rows if NULL occurred

问题描述

一个理论问题...

当下面给出的一组查询被触发时......

When a set of queries as given below is fired then...

Create table Temp1(C1 varchar(2))
Create table Temp2(C1 varchar(2))
insert into Temp1 Values('A'),(NULL),('B')
insert into Temp2 Values('B'),(NULL),('C'),(NULL)

select *from Temp1 A,Temp2 B
where A.C1 <> B.C1

...给...

我使用了 A.C1 <>Where 子句中的 B.C1.<小时>但我希望...

I used A.C1 <> B.C1 in the Where clause.

---

But I expect...

为了获得预期结果作为输出，我需要使用 ISNULL(A.C1,'') ISNULL(A.C1,'') <>Where 子句中的 ISNULL(B.C1,'').

To get expected result as output I needed to use ISNULL(A.C1,'') <> ISNULL(B.C1,'') in the Where clause.

我的问题是为什么我每次都需要使用 ISNULL 来获得预期的输出，因为 NULL 不等于任何字符串数据.

My Question is why do I need to use ISNULL every time for getting output as expected, as NULL is not equal to any string data.

推荐答案

引用自 这里 是完美的:

理解 NULL 的正确方法是它不是一个值.不是这是一个 NULL 值"但这个 NULL 不是一个值".一切要么是一个值，要么不是.当某物是一个值时，它是1"，或你好"、绿色"或$5.00"等——但当某事不是价值，它根本不是什么.SQL 表示这没有值"由特殊的非值 NULL.当有人说NULL价值，"一个人应该在精神上不同意，因为没有这样的事情.NULL 是完全没有任何值.

The correct way to understand NULL is that it is not a value. Not "this is a NULL value" but "this NULL is not a value." Everything either is a value, or it isn’t. When something is a value, it is "1," or "hello," or "green," or "$5.00″ etc — but when something isn’t a value, it just isn’t anything at all. SQL represents "this has no value" by the special non-value NULL. When someone says "the NULL value," one should mentally disagree, because there’s no such thing. NULL is the complete, total absence of any value whatsoever.

Null 不等于任何东西，因此比较总是失败.

Null is not equal to anything, therefore comparison always fails.

例如试试这个查询:

select *
from Temp2 B
where B.C1 = null 

它不会返回任何行！

必须使用的句柄空值is null 或is not null:

The handle nulls you have to use is null or is not null:

select *
from Temp1 A, Temp2 B
where A.C1 <> B.C1 or (A.C1 is null and B.C1 is not null) or (B.C1 is null and A.C1 is not null)

返回与 ISNULL 查询返回的值完全相同的值.

return exactly the same values that your query with ISNULL returns.

这篇关于如果发生 NULL，则 Where 子句拒绝行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！