## SQL WHERE clauses: Avoid CASE, use Boolean logic

As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient.

Learning some good boolean logic techniques will go a long way towards making your queries more efficient, and you won't need to rely on CASE's and other methods of doing conditional logic.

First off, when I say “conditional logic”, I am talking about something like this:

“If A then B”

where A and B are both conditions. For example, in a WHERE clause, you might want to implement a condition like this:

“If (@ReturnAll <>1) THEN (EmpID = @EmpID)”

In other words, if the @ReturnAll parameter is 1, then return all of the rows, but if @ReturnAll is not 1, then only return rows where EmpID is equal to the @EmpID parameter supplied.

To express this logic in the WHERE clause, many people might code it like this:

WHERE EmpID = CASE WHEN @ReturnAll<>1 THEN @EmpID ELSE EmpID END

However, this is kind of counter-intuitive (why should we check that EmpID = EmpID ?) and can be really tough to implement when the condition spans more than 1 column in the table (you need multiple CASE's). Also, if EmpID is null this will fail.

The alternative is to translate the condition into a regular boolean expression using only AND, OR and NOT. The logical translation of “IF A then B” is:

“(Not A) or B”

If you work it out on paper, you will see it makes sense. To translate our WHERE clause requirement using the above logic, it becomes:

WHERE (@ReturnAll =1) OR (EmpID = @EmpID)

We are saying if @ReturnAll is 1, don't bother evaluating more of the condition -- return the row because the expression is TRUE. But if @ReturnAll <> 1, then EmpID must be equal to @EmpID for the condition to be true and the row to be returned. Exactly the conditional logic we wish to express.

To show another example, suppose we wish to say:

“IF @Filter=1 THEN Date= @Date and Cust= @Cust and Emp= @Emp”

Expressing this in a CASE clause results in:

WHERE Date = CASE WHEN @Filter=1 THEN @Date ELSE Date END AND

Cust = CASE WHEN @Filter=1 THEN @Cust ELSE Cust END AND

Emp = CASE WHEN @Filter=1 THEN @Emp ELSE Emp END

A little hard to read and quite inefficient -- all 3 case expressions must be evaluated for each row in the result set. Without CASE, we get:

WHERE @Filter<>1 OR (Date= @Date and Cust= @Cust and Emp= @Emp)

Much easier to read and maintain, and faster -- if @Filter <>1, the rest of the expression can be ignored by the optimizer.

This is often implemented using ISNULL() or COALESCE() like this:

This is basically the same as writing a CASE expression in that it will not use an index on our column and doesn't implement solid boolean logic.

Converting that IF to a simple boolean expression results in a nice WHERE clause of:

WHERE (@CustID is null OR CustID = @CustID)

Finally, to express:

you would write it as:

((Not A) or B) AND (A or C)

a little harder, but it does the job! No need for CASE in the WHERE clause ... trust me !

(of course, you may need CASE to manipulate some columns or expressions in the WHERE, just don't use it for boolean logic).UPDATE: Much more on optimizing your conditional WHERE clauses here, including implementing more complex conditional parameters.

# Legacy Comments

DavidM
2003-11-17 |
re: WHERE clause logic Nice post Jeff.. It is a rare thing to see boolean logic in SQL! Perhaps you should also post the truth tables for people who don't know what they are. |

Steve Kass
2003-11-17 |
re: WHERE clause logic FYI, there is no guarantee that WHERE <filter 1> OR <filter 2> will be optimized so that the filters are evaluated in the order typed. SQL isn't that kind of language. Your best shot at getting the order as desired is with CASE: select ... where 1 = CASE WHEN <filter 1> THEN 1 WHEN <filter 2> THEN 1 ELSE 0 END Try select * from Northwind..Orders where CustomerID = CustomerID and OrderID > 1/(0*year(getdate())) SK |

Steve Kass
2003-11-17 |
re: WHERE clause logic ... should be OR in the last line there - it still divides by zero even though the first filter clause is true. |

Jeff
2003-11-17 |
re: WHERE clause logic Good point ... i guess I hadn't tested enough situations. Funny that sometimes it does it, sometimes it doesn't. Either way, it is good to use sound boolean algebra rather than forcing CASE expressions all over the WHERE clause for the reasons I mentioned. The potential benefit of "short-circuiting" and making evaluation more efficient is just a nice side-effect. |

SQL Sensei
2004-06-04 |
re: WHERE clause logic The real issue is which method performs better. For certain cases placing EmpID = EmpID in the WHERE clause can improve performance. Also, do not forget about the COALESCE function. |

Amrik Aidan
2006-05-02 |
re: WHERE clause logic Is this possible ? select a,b,c from x where 1=1 and (case when 'a'='b' then x.aa=1 when 'a'='a' then x.aa=2 and x.bb=2 end ) / I'm trying similar sort of thing but it kept giving me error and I've to change my sql to run my above requirement. Please let me know if the above e.g is possible at all ? Thanks, Amrik Aidan |

Nishad
2006-06-07 |
re: WHERE clause logic I was searching for a good replacement for the case statement in where condition. I got it.... Thank you very much for posting this logic... Thanks Nishad |

Stelban
2007-07-11 |
re: WHERE clause logic Jeff, thank you; this was extremely helpful! I have a SQL Reporting Services report which does an outer join on 'course'. I give them a drop down menu in which the users can select @parm ='yes' to pull only people who have completed the course or @parm='no' to select everyone in the org regardless of completion. I used your logic this way: select ........where (@parm='y' and course is null) or @parm='n' . Simple! Thanks again, Stelban |

RT
2007-10-10 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic How can I put the boolean method into practice in the following scenario: I am adding two parameters to an already existing report which has an existing where clause in its dataset query. I'm adding an "alltenants" param, and a "tenants" param which has the options of "Current" and "Past". Understandably, if "alltenants" is true, I wish to retrieve all tenants. If it is false, then "current" or "past" should come in and restrict tenants that will be displayed according to their start and end dates of their terms (having todays date between the two for current etc....) Please let me know asap.... Thanks! |

Jeff
2007-10-10 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic RT -- you just described exactly how to write it. Just use a basic IF check, and set the start/end date range appropriately. |

Darshan Altekar
2007-10-22 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Hey can any one please tell me if this is posible declare @temp int set @temp=1 select * from application A where A.userid = Case 1 When 1 Then = NULL ELSE LIKE '%test' ie i want to use = in one case & like in another case? |

Jeff
2007-10-22 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Darshan -- read the article! |

Thaabiet
2008-08-22 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Thank you for a fantastic article. Is it possible to use boolean login in an ORDER BY clause instead of a CASE statement? |

Prasanna
2008-09-18 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Good post Jeff !! I had one question though. I am trying to implement something like below SELECT DISTINCT COMID ,COMKEY FROM COMPANY WHERE ( IF (@XEMP = 'TRUE') THEN (EMP = 'TER') ) OR ( IF (@XEMP = 'FALSE') THEN (EMP <> 'TER') ) ORDER BY COMID Is it possible to implement some logic like that or not. Because what I am trying basically is based on a boolean parameter, display the fields where EMP is equal to 'TER' in one case and EMP is not equal to 'TER" in another case. I would really appreciate your help and time. Thank you !! |

Jeff S
2008-09-18 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Prasanna -- Again, it is just boolean logic. Work out the possibilities you want to allow on paper. Something like this would work: where (@XEMP='TRUE' and EMP='TER') OR (@XEMP != 'TRUE' and EMP != 'TER') |

Keshav
2008-10-03 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic I want to achieve this query : Where if(@SomeValue is not empty) Then DBValue like @SomeValue Else ( (DBValue like @SomeValue) or (DBValue is null) ) |

Simi Sreedharan
2008-10-10 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Good article. Thanks |

Hrishi
2008-10-14 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Jeff, this really helped! |

Jonathan
2008-12-31 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic First of all, thanks! this article is great! Maybe I'm not seeing the logic, but here is my problem: I want to check a param, if it is null, then return all, else return param. for example: IF @Param IS NULL THEN RETURN All ELSE RETURN @Param This is what Im using for my WHERE Clause: WHERE @Param IS NULL OR FieldID IN (SELECT element FROM udf_SplitChar(@Param, ',')) However, when @Param is not empty, it returns correct number of records. But when @Param is empty it returns nothing. Im trying to get it to return ALL records when empty or just records of the passed in @Param Thanks |

Amol
2009-01-03 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Great Stuff !!!! It solves my problem like Nothing Thanks |

Stanley
2009-03-18 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Hi all, I’m trying to implement a conditional filter based on certain values of parameter. Something along the lines of this: Where if @paramter = somevalue then DBfield != value a and DBfield != value b How do I do I make this cater for the database field to return multiple values for a certain condition? |

Divya
2009-05-05 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic hi all.. iam trying for a sql, for which i am unable get the desired results.. (case when ValueType ='HEX' then HEX(Value) ELSE Value end) as test where the Value is a decimal, but in case the ValueType (its a diff column) is HEX , the value should be displayed in HEx form.. else as a decimal... plz help... |

Asha
2009-05-22 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Hey nice POST! Good Work!!!! :) |

Dave M
2009-07-24 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Great post. Thank you! |

Gad Hazum
2009-08-19 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Great and well written post! Excellent reference article with all the necessary examples to help in implementing some of the more complex, yet efficient, stored procedures I wrote. Thanks for taking the time to post this! |

VS
2009-09-22 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic THANK YOU! |

Nigel
2009-09-23 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Good article! What about a conditional WHERE where the fields change given a parameter(s). My example... Parameters in... @Status int (will be a 1 or 0) @StartDate datetime @EndDate datetime Required WHERE logic... IF @Status = 1 THEN WHERE Status = @Status AND PurchaseDate >= @StartDate AND PurchaseDate <= @EndDate ELSE WHERE Status = @Status AND SaleDate >= @StartDate AND SaleDate <= @EndDate ...notice "PurchaseDate" and "SaleDate" chnage depending on the value of @Status (NOTE : there are other items in the WHERE statement but there aren't dependent on @Status). Many thanks in advance! Cheers Nigel |

Janos
2009-10-30 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic It seems like CASE is still sometimes the easiest way to express things. For instance if your where clause is (a is null or @a is null or a=@a) and (b is null or @b is null or b=@b) and ... with many such subclauses, then it's much easier (more concise) to write 0 = case when a!=@a or b!=@b or ... then 1 else 0 end |

Bindu
2009-12-02 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Awesome post! And very helpful. |

customer
2010-03-09 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic How about this usage of a conditional translated to a boolean statement following you transformation rules: select CAST(((a.[nme] NOT LIKE 'Gas' AND 'G') OR (a.[nme] LIKE 'Gas' AND 'X')) AS VARCHAR) from [linq_grp] as a Unfortunately, I see no way to accomplish this. Can you help me / us `? |

sindhu
2010-03-20 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic just amazing ... great help to all . |

Pablo Aizpiri
2010-03-29 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Hello, I've used this method for months but I realized that this can many times be a VERY INEFFICIENT method of retrieving data, as the optomized is NOT GUARANTEED to have "the rest of the expression [...] ignored by the optimizer" as you point out. For example, in a recursize hierarchy where you may have parent-child relationships and you wish to include a flag to designate wether children should be returned or not, according to this post I would write the query as follows: [...] WHERE (@IncludeChildren = 0 AND c.companyid = @CompanyID) OR ( @IncludeChildren = 1 AND c.companyid in (select companyid from fctnGetCompanyListHierarchy(@CompanyID)) ) If this is a top parent in a large higherarchy, (As it is in some work I have done), this query will be terribly slow if you decide to include all children, however if you choose to just return the company without children the optomizer is not guaranteed to ignore the second clause and in my case, the procedure becomes terribly slow. Please make sure you test you queries without a boolean clause! This can be many times EXTREMELY unefficient! -KTF |

Petter
2010-04-06 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Great post! Thanks a lot! Petter, Sweden |

sarfraz
2010-05-05 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic GREAT Article and also explained very well. Good JOB! |

steven
2010-06-14 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic it's can use in MS SQL?? |

ShakaKhan
2010-06-18 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Thank you for your post. It's very helpful. Great Job! |

Nanto
2010-07-16 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic Great post helps me a lot to optimize queries. Thanx for the job |

Mayank
2010-09-26 |
re: SQL WHERE clauses: Avoid CASE, use Boolean logic one more thing that you can achieve with using boolean logic in where clause is using IN for eg :- in case statement u can't use IN where EMP in Case <?> = Then <c> else <d> End ---- this will throw an error But you can use IN with Boolean Logic ((A) or (Emp in Select From Table1>) |