問題描述
我想將參數(shù) @CompanyID 傳入 where 子句以過濾結(jié)果.但有時(shí)這個(gè)值可能是 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)
我發(fā)現(xiàn)第一個(gè)版本是最快的，但我也發(fā)現(xiàn)在其他表中使用類似的方法我得到了不同的結(jié)果集.我不太明白兩者之間的區(qū)別.
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.
誰能解釋一下?
推薦答案
好吧，兩個(gè)查詢都在處理相同的兩個(gè)場(chǎng)景 -
在一種情況下，@CompanyID 包含一個(gè)值，
并且在第二個(gè) @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.
對(duì)于這兩個(gè)查詢，第一個(gè)場(chǎng)景將返回相同的結(jié)果集 - 因?yàn)槿绻?@CompanyId 包含一個(gè)值，兩者都將返回 companyId = @CompanyId 的所有行，但是第一個(gè)查詢可能會(huì)更快地返回它(在我的答案末尾有更多信息).
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).
然而，第二種情況是查詢開始表現(xiàn)不同.
The second scenario, however, is where the queries starts to behave differently.
首先，這就是你得到不同結(jié)果集的原因:
First, this is why you get different result sets:
結(jié)果集的差異
版本 1
WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID)
當(dāng)@CompanyID為空時(shí)，where子句不會(huì)過濾任何行，返回表中的所有記錄.
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)
當(dāng)@CompanyID為null時(shí)，where子句會(huì)過濾掉CompanyID為null的所有行，因?yàn)?code>null = null的結(jié)果> 實(shí)際上是 unknown - 任何帶有 null = null 的查詢都不會(huì)返回任何結(jié)果，除非 ANSI_NULLS 設(shè)置為 OFF(你真的不應(yīng)該這樣做，因?yàn)樗驯粭売?.
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).
索引使用
您可能會(huì)從第一個(gè)版本中獲得更快的結(jié)果，因?yàn)樵?where 子句中的列上使用任何函數(shù)都會(huì)阻止 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.
結(jié)論
版本 1 優(yōu)于版本 2.即使您不想返回 companyId 為空的記錄，最好寫為 WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID) AND CompanyID IS NOT NULL而不是使用第二個(gè)版本.
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.
這篇關(guān)于使用 IS NULL 或 Coalesce 將參數(shù)傳遞給 where 子句的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！