SQL 检查 NULL 或 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3988249/
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
To check on NULL or 0
提问by Incognito
What is faster in SQL to check value for NULL or 0
在 SQL 中检查 NULL 或 0 值的速度更快
I want to have the fastest way to check is value already in table.
我想以最快的方式检查表中已经存在的值。
For example which is faster :
例如哪个更快：
IF ((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers]
WHERE VALUE = @myVal) is null )
BEGIN
....
END
ELSE
BEGIN
....
END
or
或者
IF ((SELECT COUNT(ID) FROM [SomeTable].[dbo].[BlockedSubscriberNumbers]
WHERE VALUE = @myVal) > 0 )
BEGIN
....
END
ELSE
BEGIN
....
END
Also does in T-SQL plays role where the frequent accruing case must be. I mean is it will be faster that in most cases it will fail into IF block and slower if it will mostly go into ELSE.
在 T-SQL 中也确实在频繁发生的情况下发挥作用。我的意思是，在大多数情况下，它会更快地进入 IF 块，而如果它主要进入 ELSE，则速度会更慢。
回答by msarchet
Well these will actually do different things, you can't check if a NULLis greater than 0 in SQL.
那么这些实际上会做不同的事情，你不能NULL在 SQL 中检查 a是否大于 0。
What you should do is this.
你应该做的是这个。
IF (ISNULL((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers]
WHERE VALUE = @myVal), 0) > 0 )
BEGIN
....
END
ELSE
BEGIN
....
END
And did you actually mean equals 0? Because your question states
你的意思是等于0吗？因为你的问题说明
What is faster in SQL to check value for NULL or 0
在 SQL 中检查 NULL 或 0 值的速度更快
This part is in regards to Joe's comment about multiple result sets
这部分是关于乔对多个结果集的评论
You could do some kind of aggreate function over the Select ID using the ISNULL to determine whether or not any of the values are greater than 0 (assuming of course that all of your values are greater than 0).
您可以使用 ISNULL 对 Select ID 执行某种聚合函数，以确定是否有任何值大于 0（当然假设您的所有值都大于 0）。
In regards to figuring out if any rows contained that information per the OPs comment
关于根据 OP 评论确定是否有任何行包含该信息
IF (Select Count(ID) from [SomeTable].[dbo].[BlockedSubscriberNumbers] Where Value = @myVal) = 0Shoud tell you if there are any Rows containing that value
IF (Select Count(ID) from [SomeTable].[dbo].[BlockedSubscriberNumbers] Where Value = @myVal) = 0应该告诉你是否有任何包含该值的行
Final Edit
最终编辑
Just use Exists
只需使用 Exists
If Exists(Select ID From [SomeTable].[dbo].BlockedSubscriberNumbers] Where Values = @myVal)
If Exists(Select ID From [SomeTable].[dbo].BlockedSubscriberNumbers] Where Values = @myVal)
回答by Jo?o Pereira
Checking for NULL is much faster than checking for 0, but I think that, for those queries, we're talking about different things: they willproduce different results.
检查 NULL 比检查 0 快得多，但我认为，对于这些查询，我们谈论的是不同的事情：它们会产生不同的结果。
回答by CodeNeedsCoffee
For anyone who want's it within the Query, you can do something like:
对于任何想要在查询中使用它的人，您可以执行以下操作：
SELECT ISNULL(NULLIF(primaryValue,0),secondaryValue) as Value FROM SomeTable
SELECT ISNULL(NULLIF(primaryValue,0),secondaryValue) as Value FROM SomeTable
Here the NULLIF will return primaryValueas NULL only if it is already NULL or if it is 0. The ISNULL will return secondaryValueif primaryValueis NULL.
此处 NULLIFprimaryValue仅在它已经为 NULL 或为 0 时才会返回为 NULL。secondaryValue如果primaryValue为 NULL，则ISNULL 将返回。
Thus, if primaryValueis NULL or 0, then it will return secondaryValue.
因此，如果primaryValue是 NULL 或 0，那么它将返回secondaryValue。
回答by Gennady Vanin Геннадий Ванин
IMO, each record/row in table contains NULL BITMAP (2 bytes, each bit of which tells about null-ness of one/each of the column's value in this row), so before selecting/reading real stored value this process of reading data passes this checking/reading of the corresponding bit from this NULL bit map.
IMO，表中的每条记录/行都包含 NULL BITMAP （2 个字节，其中的每一位都说明该行中一个/每个列的值是否为空），因此在选择/读取实际存储值之前，此读取数据的过程通过此 NULL 位图中相应位的检查/读取。
In case of NULL (or, in other words, "is not null" check), the reading process stops at this stage, while other selects/checks/comparison might(or might not, this depends) continue, so "is null check" cannot be slower. Even more, NULL values at the end of the row are not even stored, no storage is occupied by them. They are virtually and, sometimes, practically nothing.
在 NULL 的情况下（或者，换句话说，“is not null”检查），读取过程在这个阶段停止，而其他选择/检查/比较可能（或可能不会，这取决于）继续，所以“是空检查“不能再慢了。更重要的是，行尾的 NULL 值甚至不存储，它们不占用任何存储空间。它们实际上是，有时几乎什么都不是。
Though, the problem is that your TSQL examples in question and question itself are ambiguous with possible multiple interpretation and answers.
但是，问题在于您的 TSQL 示例和问题本身对于可能的多种解释和答案是模棱两可的。
回答by Michael Buen
EXISTS could be faster than COUNT, especially if the rows you are looking for is very large, and besides you should not dwell too much on micro-optimizations. Strive for code readability first, so others reading your code can easily glean the intent of your query. Anyway, COUNT will still attempt to loop the rows even it already find the value you are looking for. EXISTS is a directive for your RDBMS to stop searching as soon as it matches your criteria.
EXISTS 可能比 COUNT 更快，尤其是当您要查找的行非常大时，此外您不应该过多地关注微优化。首先争取代码可读性，以便其他阅读您代码的人可以轻松地收集您查询的意图。无论如何， COUNT 仍然会尝试循环行，即使它已经找到了您要查找的值。EXISTS 是 RDBMS 的指令，一旦它符合您的条件就停止搜索。
And besides, the logic of your code is if something exists, that's why there's a first-class language construct introduced to the language to facilitate that. And of course database vendor's engineers will put effort to optimize that, given that it is basically a solved problem. You will not expect them to re-use their COUNTengine for EXISTS functionality, it would be pretty bad if EXISTS waited for the COUNT and its result, and then check if that result is greater than zero to make EXISTSreturn true. That won't happen.
此外，您的代码的逻辑是如果某些东西存在，这就是为什么在语言中引入了一流的语言结构来促进这一点。当然，数据库供应商的工程师会努力优化它，因为它基本上是一个已解决的问题。您不会期望他们COUNT为 EXISTS 功能重新使用他们的引擎，如果 EXISTS 等待 COUNT 及其结果，然后检查该结果是否大于零以EXISTS返回 true ，那将是非常糟糕的。那不会发生。
EXISTS is pretty optimized
EXISTS 非常优化
This is better, both in readability and performance:
这在可读性和性能方面都更好：
IF EXISTS(SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers]
WHERE VALUE = @myVal)
And the following can result to many rows. If by any chance many rows in your table satisfy the filter, this will result to error:
以下可能导致多行。如果你的表中有很多行满足过滤器，这将导致错误：
IF ((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers]
WHERE VALUE = @myVal) is null )
The only redeeming factor of that query approach is if the VALUE field on your table is a primary key(but let's say you forgot to put primary key on that table), and it return two rows, that would not do a silent error, the RDBMS shall balk with error, and you can fix the error earlier compared to COUNTand EXISTSapproach
该查询方法的唯一补救因素是如果您表上的 VALUE 字段是主键（但假设您忘记将主键放在该表上），并且它返回两行，这不会导致无提示错误， RDBMS应不惜错误，并与您可以修复错误较早COUNT和EXISTS方法
回答by M.Nabeel
select (case when id is null or id=0
then (dothis) else (dothis) end) as idState
from [SomeTable].[dbo].[BlockedSubscriberNumbers]
