Monday, July 18, 2011

Conditional Filtering: Using conditions in T-SQL WHERE clauses

Many of us have some time or another come across the scenario where we would like to use if statements in a where clause.

Here are 3 ways (that I know of) on how you can conditionally filter your where clause in T-SQL.

Using multiple queries seperated by IF Clauses

DECLARE @ContactID int 
SET @ContactID = 5

IF(@ContactID IS NULL)
BEGIN
 SELECT * FROM [AdventureWorks].[Person].[Contact]
END
ELSE
BEGIN
 SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = @ContactID
END

This must be one of the easiest ways to retrieve data depending on a variable and can also be the fastest way also.  Unfortunately, if you have a lot of variables, this could become a maintenance nightmare.  Imagine if you have to add another variable, the query will have to be repeated again, not to mention that if the query has to be amended, this needs to be done for every occurrence.

Using the Case expression


DECLARE @ContactID int
SET @ContactID = 5

SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = CASE WHEN @ContactID IS NULL THEN ContactID ELSE @ContactID END

Although not so straightforward, many developers opt to use this as it is much more readable and you only need one query.  I have used this for a number of years and found it extremely useful until I found out about the last option

Using boolean logic


DECLARE @ContactID int
SET @ContactID = NULL

SELECT * FROM [AdventureWorks].[Person].[Contact] 
WHERE @ContactID IS NULL OR ContactID = @ContactID

This method only uses Ands & Ors in order to get the result.  It takes a while to get used to but I highly recommend this method as it is much faster due to the conversion to simple boolean logics and it is also readable and easily maintained.

For more information visit http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx


7 comments:

  1. Hello,
    Great article

    It seems that a carriage return is missing in the last SQL sequence:

    DECLARE @ContactID int
    SET @ContactID = NULL

    SELECT * FROM [AdventureWorks].[Person].[Contact]
    WHERE @ContactID IS NULL OR ContactID = @ContactID

    ReplyDelete
  2. Thanks, good to hear you found it interesting. Amended the SQL statement :)

    ReplyDelete
  3. It seems that the second and third method is much slower in performance than the first one.

    I have a complex query with lots of records and I measured when using the "IS NULL OR .." option it was in my case 5(!) times slower than using the first option.
    Perhaps SQL Server is not able to optimize the "@var IS NULL" away (2008 R2) in case @var is set.

    ReplyDelete
  4. 5 times slower is a big difference! Will check regarding the NULL.

    Usually I would go for the maintainability option but if it effects performance that much, I guess the first option is the way to go.

    ReplyDelete
  5. Looked all over the place trying to figure out how to solve a problem with SSRS. Tried using coalesc, case statements, etc. and your Boolean logic suggestion was the only way to get everything to work properly. And it is so simple!

    Thanks for the great article! I know I will end up using this method again.

    ReplyDelete
  6. I found this a lot simpler: SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ISNULL(@ContactID, ContactID) = ContactID;

    ReplyDelete