在 SQL 中比较 NULL 或列值的有效方法
在 SQL 查询中检查列是否为空或值的有效方法是什么？考虑一个带有整数列 column 且有索引的 SQL 表 table。 @value 可以是某个整数或 null，例如：16 或 null。
查询1：不确定，但似乎不应该依赖SQL中的短路。但是，当 @value 为某个整数或 null 时，下面的查询始终可以正常工作。
select * from
table
where (@value is null or column = @value)
以下查询是上述查询的扩展版本。它也能正常工作。
select * from
table
where ((@value is null)
or (@value is not null and column = @value))
上述两个查询会利用索引吗？
查询 2：以下查询将列与非空 @value 进行比较，否则将列 column 与其自身进行比较，后者始终为 true 并返回所有内容。它也能正常工作。这个查询会利用索引吗？
select * from
table
where (column = isnull(@value, column))
最好的办法是什么？
注意：如果答案因数据库而异，我对 MS-SQL 感兴趣。
What's the efficient way to check for a null or value for a column in SQL query. Consider a sql table table with integer column column which has an index. @value can be some integer or null ex: 16 or null.
Query 1: Not sure, but it seems one should not rely on the short-circuit in SQL. However, below query always works correctly when @value is some integer or null.
select * from
table
where (@value is null or column = @value)
The below query is an expanded version of the above query. It works correctly too.
select * from
table
where ((@value is null)
or (@value is not null and column = @value))
Would the above 2 queries would take the advantage of the index?
Query 2: The below query compares the column with non-null @value else compares the column column with itself which will always be true and returns everything. It works correctly too. Would this query take advantage of the index?
select * from
table
where (column = isnull(@value, column))
What's the best way?
Note: If the answer varies with databases, I'm interested in MS-SQL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在过去的几天里，这个问题的变体出现了好几次（为什么这些事情总是成群结队地发生？）。简短的回答是，是的，如果 SQL Server 创建具有已知值的查询计划，它将短路逻辑。因此，如果您在设置变量的脚本中有该代码，那么我相信它应该使逻辑短路（确保测试）。但是，如果是在存储过程中，那么SQL Server会提前创建一个查询计划，并且它不知道是否可以短路查询，因为它不知道生成时的参数值查询计划。
不管它是否短路，如果索引是查询的唯一部分，那么 SQL Server 应该能够使用该索引。如果变量为 NULL，那么您可能不希望 SQL Server 使用索引，因为它毫无用处。
如果您使用存储过程，那么最好的选择是在查询中使用 OPTION (RECOMPILE)。这将导致 SQL Server 每次创建一个新的查询计划。这是一点点开销，但收益通常会超过它很多。这仅适用于 SQL 2008，甚至仅适用于某些更高版本的服务包。在此之前，RECOMPILE 存在一个错误，导致其无用。有关更多信息，请查看 Erland Sommarskog 关于该主题的精彩文章。具体来说，您需要查看静态 SQL 部分。
Variations on this question have come up several times in the past couple of days (why do these things always happen in groups?). The short answer is that yes, SQL Server will short-circuit the logic IF it creates the query plan with known values. So, if you have that code in a script where the variables are set then I believe it should short-circuit the logic (test to be sure). However, if it's in a stored procedure then SQL Server will create a query plan ahead of time and it won't know whether or not it can short-circuit the query, because it doesn't know the parameter values at the time of generating the query plan.
Regardless of whether it is short-circuited or not, SQL Server should be able to use the index if that's the only part of your query. If the variable is NULL though, then you probably don't want SQL Server using the index because it will be useless.
If you're in a stored procedure then your best bet is to use OPTION (RECOMPILE) on your query. This will cause SQL Server to create a new query plan each time. This is a little bit of overhead, but the gains typically outweigh that by a lot. This is ONLY good for SQL 2008 and even then only for some of the later service packs. There was a bug with RECOMPILE before that rendering it useless. For more information check out Erland Sommarskog's great article on the subject. Specifically you'll want to look under the Static SQL sections.
澄清一点，SQL 并不像我们所知道的基于 C 的语言那样存在短路。看起来像短路的实际上是在 SQL Server 三元逻辑中 TRUE OR NULL 的计算结果为 TRUE
例如：
To clarify a point, SQL doesn't really have a short circuit as we know it in C-based languages. What looks like a short circuit is really that in SQL Server ternary logic TRUE OR NULL evaluates to TRUE
Eg: