Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

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. 

Another common situation is using a single variable to implement the optional filter.  For example:

"IF @CustID is not null THEN CustID = @CustID"

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

WHERE CustID = ISNULL(@CustID, CustID)


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)

which, again, is the preferred way to implement this type of logic in SQL.   It is short, simple, portable, easy to read and maintain, and efficient.

Finally, to express:

IF A THEN B ELSE C


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