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.
我想将参数@CompanyID传递给where子句来过滤结果。但有时这个值可能为null,所以我希望返回所有记录。我找到了两种方法,但不确定哪种方法最安全。
Version 1
SELECT ProductName, CompanyID
FROM Products
WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID)
Version 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.
我发现第一个版本是最快的,但我也发现在其他表中使用类似的方法,我得到了不同的结果集。我不太明白两者之间的差异。
Can anyone please explain?
有人可以解释一下吗?
3 个解决方案
#1
1
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中包含NULL。
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).
对于这两个查询,第一个场景将返回相同的结果集 - 因为如果@CompanyId包含一个值,则两者都将返回companyId = @CompanyId的所有行,但是第一个查询可能会更快地返回它(更多内容在我的末尾)回答)。
The second scenario, however, is where the queries starts to behave differently.
但是,第二种情况是查询开始表现不同。
First, this is why you get different result sets:
首先,这就是为什么你得到不同的结果集:
Difference in result sets
结果集的差异
Version 1
WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID)
When @CompanyID is null, the where clause will not filter out any rows whatsoever, and all the records in the table will be returned.
当@CompanyID为null时,where子句不会过滤任何行,并且将返回表中的所有记录。
Version 2
WHERE CompanyID = COALESCE(@CompanyID, CompanyID)
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).
当@CompanyID为null时,where子句将过滤掉CompanyID为null的所有行,因为null = null的结果实际上是未知的 - 并且任何具有null = null的查询,因为它的where子句将不返回任何结果,除非ANSI_NULLS是设置为OFF(由于它已被弃用,你真的不应该这样做)。
Index usage
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.
您可能会从第一个版本获得更快的结果,因为在where子句中使用列上的任何函数都会阻止SQL Server使用您在此列上可能具有的任何索引。您可以在MSSql Tips中的这篇文章中阅读更多相关内容。
Conclusion
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.
版本1优于版本2.即使您不想返回companyId为null的记录,仍然可以写为WHERE(@CompanyID IS NULL或CompanyID = @CompanyID)并且CompanyID IS NOT NOT比使用第二个版本更好。
#2
1
It's worth noting that using the syntax ([Column] = @Value OR [Column] IS NULL) is a much better idea than using ISNULL([Column],@Value) = @Value (or using COALESCE).
值得注意的是,使用语法([Column] = @Value OR [Column] IS NULL)比使用ISNULL([Column],@ Value)= @Value(或使用COALESCE)更好。
This is because using the function causes the query to become un-SARGable; so indexes won't be used. The first expression is SARGable, and thus, will perform better.
这是因为使用该函数会导致查询变为un-SARGable;所以不会使用索引。第一个表达式是SARGable,因此表现更好。
Just adding this, as the OP states "I have found that the first version is the quickest", and wanted to elaborate why (even though, currently the statement is incomplete, I am guessing this was more due to user error and ignorance).
只是添加这个,因为OP声明“我发现第一个版本是最快的”,并想详细阐述原因(即使目前声明不完整,我猜这更多是由于用户错误和无知)。
#3
0
The second version is not correct SQL (for SQL Server). It needs an operator. Presumably:
第二个版本不正确的SQL(对于SQL Server)。它需要一个操作员。想必:
SELECT ProductName, CompanyID
FROM Products
WHERE COALESCE(@CompanyID, CompanyID) = CompanyID;
The first version is correct as written. If you have an index on CompanyID, you might find this faster:
第一个版本正如写的那样。如果您有CompanyID的索引,您可能会更快地找到它:
SELECT *
FROM Products
WHERE CompanyID = @CompanyID
UNION ALL
SELECT *
FROM Products
WHERE @CompanyID IS NULL;
#1
1
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中包含NULL。
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).
对于这两个查询,第一个场景将返回相同的结果集 - 因为如果@CompanyId包含一个值,则两者都将返回companyId = @CompanyId的所有行,但是第一个查询可能会更快地返回它(更多内容在我的末尾)回答)。
The second scenario, however, is where the queries starts to behave differently.
但是,第二种情况是查询开始表现不同。
First, this is why you get different result sets:
首先,这就是为什么你得到不同的结果集:
Difference in result sets
结果集的差异
Version 1
WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID)
When @CompanyID is null, the where clause will not filter out any rows whatsoever, and all the records in the table will be returned.
当@CompanyID为null时,where子句不会过滤任何行,并且将返回表中的所有记录。
Version 2
WHERE CompanyID = COALESCE(@CompanyID, CompanyID)
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).
当@CompanyID为null时,where子句将过滤掉CompanyID为null的所有行,因为null = null的结果实际上是未知的 - 并且任何具有null = null的查询,因为它的where子句将不返回任何结果,除非ANSI_NULLS是设置为OFF(由于它已被弃用,你真的不应该这样做)。
Index usage
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.
您可能会从第一个版本获得更快的结果,因为在where子句中使用列上的任何函数都会阻止SQL Server使用您在此列上可能具有的任何索引。您可以在MSSql Tips中的这篇文章中阅读更多相关内容。
Conclusion
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.
版本1优于版本2.即使您不想返回companyId为null的记录,仍然可以写为WHERE(@CompanyID IS NULL或CompanyID = @CompanyID)并且CompanyID IS NOT NOT比使用第二个版本更好。
#2
1
It's worth noting that using the syntax ([Column] = @Value OR [Column] IS NULL) is a much better idea than using ISNULL([Column],@Value) = @Value (or using COALESCE).
值得注意的是,使用语法([Column] = @Value OR [Column] IS NULL)比使用ISNULL([Column],@ Value)= @Value(或使用COALESCE)更好。
This is because using the function causes the query to become un-SARGable; so indexes won't be used. The first expression is SARGable, and thus, will perform better.
这是因为使用该函数会导致查询变为un-SARGable;所以不会使用索引。第一个表达式是SARGable,因此表现更好。
Just adding this, as the OP states "I have found that the first version is the quickest", and wanted to elaborate why (even though, currently the statement is incomplete, I am guessing this was more due to user error and ignorance).
只是添加这个,因为OP声明“我发现第一个版本是最快的”,并想详细阐述原因(即使目前声明不完整,我猜这更多是由于用户错误和无知)。
#3
0
The second version is not correct SQL (for SQL Server). It needs an operator. Presumably:
第二个版本不正确的SQL(对于SQL Server)。它需要一个操作员。想必:
SELECT ProductName, CompanyID
FROM Products
WHERE COALESCE(@CompanyID, CompanyID) = CompanyID;
The first version is correct as written. If you have an index on CompanyID, you might find this faster:
第一个版本正如写的那样。如果您有CompanyID的索引,您可能会更快地找到它:
SELECT *
FROM Products
WHERE CompanyID = @CompanyID
UNION ALL
SELECT *
FROM Products
WHERE @CompanyID IS NULL;