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

Print | posted on Friday, November 14, 2003 12:12 PM | Filed Under [ T-SQL Techniques Efficiency ]

## Feedback

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

## # 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

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

## # 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

## # 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

## # 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!

## # 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.## # re: SQL WHERE clauses: Avoid CASE, use Boolean logic

Hey can any one please tell me if this is posibledeclare @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?

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

Darshan -- read the article!## # 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?

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

Good post Jeff !! I had one question though. I am trying to implement something like belowSELECT 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 !!

## # 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')

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

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

Good article. Thanks## # re: SQL WHERE clauses: Avoid CASE, use Boolean logic

Jeff, this really helped!## # 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

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

Great Stuff !!!!It solves my problem like Nothing

Thanks

## # 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?

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

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

Hey nice POST!Good Work!!!! :)

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

Great post. Thank you!## # 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!## # re: SQL WHERE clauses: Avoid CASE, use Boolean logic

THANK YOU!## # 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

## # 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

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

Awesome post! And very helpful.## # 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 `?

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

just amazing ... great help to all .## # 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

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

Great post! Thanks a lot!Petter, Sweden

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

GREAT Article and also explained very well.Good JOB!

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

it's can use in MS SQL??## # re: SQL WHERE clauses: Avoid CASE, use Boolean logic

Thank you for your post. It's very helpful. Great Job!## # re: SQL WHERE clauses: Avoid CASE, use Boolean logic

Great post helps me a lot to optimize queries.Thanx for the job

## # 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 INfor 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>)