英文:
SQL Server NOT excludes null?
问题
I have a column in my table called IsDuplicate, nullable.
If I want to return all results where IsDuplicate is '0' or is null, I can do
WHERE ISNULL(IsDuplicate, 0) = 0
or I can do
WHERE IsDuplicate = '0' OR IsDuplicate IS NULL
However, this never works:
WHERE NOT(IsDuplicate = '1')
Why does this not work? This should cover both IsDuplicate equaling both '0' and NULL.
英文:
I have a column in my table called IsDuplicate, nullable.
If I want to return all results where IsDuplicate is '0' or is null, I can do
WHERE ISNULL(IsDuplicate, 0) = 0
or I can do
WHERE IsDuplicate = '0' OR IsDuplicate IS NULL
However, this never works:
WHERE NOT(IsDuplicate = '1')
Why does this not work? This should cover both IsDuplicate equaling both '0' and NULL.
答案1
得分: 2
Nulls aren't really a value. They are the absence value. You can see this in where clauses:
select *
from foo
where bar is null
instead of
select *
from foo
where bar = null
Bar could = 1, or it could be 34, or could have no value at all.
For example, I currently have 2 legs, 2 arms, and null tails. Is my tail a mouse tail? I don't have a tail to check against.
So for SQL, unless you specifically address the null they get ignored.
英文:
Nulls aren't really a value. They are the absence value. You can see this in where clauses:
select *
from foo
where bar is null
instead of
select *
from foo
where bar = null
Bar could = 1, or it could be 34, or could have no value at all.
For example, I currently have 2 legs, 2 arms, and null tails. Is my tail a mouse tail? I don't have a tail to check against.
So for SQL, unless you specifically address the null they get ignored.
答案2
得分: 1
在SQL Server中，涉及到NULL值的比较和布尔运算的处理受ANSI_NULLS选项的设置影响。当此选项为ON时，且IsDuplicate为NULL时，IsDuplicate = '1'和NOT(IsDuplicate = '1')的结果都是UNKNOWN（而非FALSE）。
要返回一行，WHERE条件应该评估为TRUE（而不是NULL或UNKNOWN）。
请查看这些链接：
- 有关涉及
NULL和UNKNOWN的布尔运算结果：https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-ver16 ANSI_NULLS选项的工作原理：https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver16- 为什么
NOT运算符从UNKNOWN返回UNKNOWN：https://learn.microsoft.com/en-us/sql/t-sql/language-elements/not-transact-sql?view=sql-server-ver16
英文:
In SQL Server the treatment of comparisons and boolean operators involving NULL values are influenced by the setting of ANSI_NULLS option. When this option is ON, and the IsDuplicate is NULL the result of both IsDuplicate = '1' and NOT(IsDuplicate = '1') is UNKNOWN (not FALSE).
For a row to be returned the WHERE condition should evaluate to TRUE (not NULL or UNKNOWN).
Check these:
Results of boolean operators involving NULLs and UNKNOWNs
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/null-and-unknown-transact-sql?view=sql-server-ver16
and how ANSI_NULLS option works
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver16
And why NOT operator returns UNKNOWN from UNKNOWN:
https://learn.microsoft.com/en-us/sql/t-sql/language-elements/not-transact-sql?view=sql-server-ver16
答案3
得分: 0
在SQL中，当比较数值时，结果可以是true、false或unknown。即存在一种“三值逻辑”。
因此，对于NOT (some_value = 1)，如果some_value是NULL，表达式的结果将是unknown而不是true或false，这意味着具有NULL值的行不会被该表达式返回。
例如，对于“where NOT(unknown)”，NOT不能将unknown转变为true或false，因此它保持unknown，并因此被排除在结果之外。
参考：SQL的三值逻辑
英文:
In SQL, when comparing values, the result can be true or false or unknown. i.e. a "3 way logic" applies.
So, for NOT (some_value = 1) if some_value is NULL, the result of the expression will be unknown instead of true or false, and this means that rows with NULL values will not be returned by this expression.
i.e. for "where NOT(unknown)"; the NOT cannot reverse unknown into true or false, so it remains unknown and is therefore excluded from the result.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论