sql - Passing in parameter to where clause using IS NULL or Coalesce

Question

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.

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?

Answer 1

Well, both queries are handling the same two scenarios -
In one scenario @CompanyID contains a value,
and in the second @CompanyID contains 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).

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.

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).

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.

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.