使用 IS NULL 或 Coalesce 将参数传递给 where 子句 [英] Passing in parameter to where clause using IS NULL or Coalesce

问题描述

我想将参数 @CompanyID 传入 where 子句以过滤结果.但有时这个值可能是 null 所以我希望返回所有记录.我找到了两种方法，但不确定哪一种最安全.

I would like to pass in a parameter @CompanyID into a where clause to filter results. But sometimes this value may be null so I want all records to be returned. I have found two ways of doing this, but am not sure which one is the safest.

版本 1

SELECT ProductName, CompanyID 
FROM Products 
WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID)

版本 2

SELECT ProductName, CompanyID 
FROM Products 
WHERE CompanyID = COALESCE(@CompanyID, CompanyID)

我发现第一个版本是最快的，但我也发现在其他表中使用类似的方法我得到了不同的结果集.我不太明白两者之间的区别.

I have found that the first version is the quickest, but I have also found in other tables using a similar method that I get different result sets back. I don't quite understand the different between the two.

谁能解释一下?

推荐答案

好吧，两个查询都在处理相同的两个场景 -
在一种情况下，@CompanyID 包含一个值，
并且在第二个 @CompanyID 中包含 NULL.

Well, both queries are handling the same two scenarios -
In one scenario @CompanyID contains a value,
and in the second @CompanyID contains NULL.

对于这两个查询，第一个场景将返回相同的结果集 - 因为如果 @CompanyId 包含一个值，两者都将返回 companyId = @CompanyId 的所有行，但是第一个查询可能会更快地返回它(在我的答案末尾有更多信息).

For both queries, the first scenario will return the same result set - since if @CompanyId contains a value, both will return all rows where companyId = @CompanyId, however the first query might return it faster (more on that at the end of my answer).

然而，第二种情况是查询开始表现不同.

The second scenario, however, is where the queries starts to behave differently.

首先，这就是你得到不同结果集的原因:

First, this is why you get different result sets:

结果集的差异

版本 1

WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID)

当@CompanyID为空时，where子句不会过滤任何行，返回表中的所有记录.

When @CompanyID is null, the where clause will not filter out any rows whatsoever, and all the records in the table will be returned.

版本 2

WHERE CompanyID = COALESCE(@CompanyID, CompanyID)

当@CompanyID为null时，where子句会过滤掉CompanyID为null的所有行，因为null = null的结果> 实际上是 unknown - 任何带有 null = null 的查询都不会返回任何结果，除非 ANSI_NULLS 设置为 OFF(你真的不应该这样做，因为它已被弃用).

When @CompanyID is null, the where clause will filter out all the rows where CompanyID is null, since the result of null = null is actually unknown - and any query with null = null as it's where clause will return no results, unless ANSI_NULLS is set to OFF (which you really should not do since it's deprecated).

索引使用

您可能会从第一个版本中获得更快的结果，因为在 where 子句中的列上使用任何函数都会阻止 SQL Server 使用您在该列上可能拥有的任何索引.您可以在 这篇文章 MSSql 技巧.

You might get faster results from the first version, since the use of any function on a column in the where clause will prevent SQL Server from using any index that you might have on this column. You can read more about it on this article in MSSql Tips.

结论

版本 1 优于版本 2.即使您不想返回 companyId 为空的记录，最好写为 WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID) AND CompanyID IS NOT NULL而不是使用第二个版本.

Version 1 is better than version 2. Even if you do not want to return records where companyId is null it's still better to write as WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID) AND CompanyID IS NOT NULL than to use the second version.

这篇关于使用 IS NULL 或 Coalesce 将参数传递给 where 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！