Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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.

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

Feedback

Gravatar

# 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.
11/17/2003 4:59 PM | DavidM
Gravatar

# 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
11/17/2003 7:26 PM | Steve Kass
Gravatar

# 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.
11/17/2003 7:28 PM | Steve Kass
Gravatar

# 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.
11/17/2003 7:38 PM | Jeff
Gravatar

# 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.
6/4/2004 4:41 PM | SQL Sensei
Gravatar

# 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
5/2/2006 11:36 AM | Amrik Aidan
Gravatar

# 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
6/7/2006 6:34 PM | Nishad
Gravatar

# 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
7/11/2007 7:12 PM | Stelban
Gravatar

# 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!
10/10/2007 4:49 PM | RT
Gravatar

# 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.
10/10/2007 4:53 PM | Jeff
Gravatar

# 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?
10/22/2007 9:14 AM | Darshan Altekar
Gravatar

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

Darshan -- read the article!
10/22/2007 9:37 AM | Jeff
Gravatar

# 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?
8/22/2008 4:46 AM | Thaabiet
Gravatar

# 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 !!
9/18/2008 6:48 PM | Prasanna
Gravatar

# 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')
9/18/2008 6:51 PM | Jeff S
Gravatar

# 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) )
10/3/2008 3:24 AM | Keshav
Gravatar

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

Good article. Thanks
10/10/2008 1:47 AM | Simi Sreedharan
Gravatar

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

Jeff, this really helped!
10/14/2008 8:33 PM | Hrishi
Gravatar

# 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
12/31/2008 9:45 AM | Jonathan
Gravatar

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

Great Stuff !!!!
It solves my problem like Nothing
Thanks
1/3/2009 6:17 AM | Amol
Gravatar

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

3/18/2009 11:15 PM | Stanley
Gravatar

# 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...
5/5/2009 7:59 AM | Divya
Gravatar

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

Hey nice POST!
Good Work!!!! :)
5/22/2009 9:08 AM | Asha
Gravatar

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

Great post. Thank you!
7/24/2009 11:44 AM | Dave M
Gravatar

# 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!
8/19/2009 10:56 AM | Gad Hazum
Gravatar

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

THANK YOU!
9/22/2009 5:10 PM | VS
Gravatar

# 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
9/23/2009 10:16 PM | Nigel
Gravatar

# 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
10/30/2009 12:31 PM | Janos
Gravatar

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

Awesome post! And very helpful.
12/2/2009 5:50 AM | Bindu
Gravatar

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

3/9/2010 5:36 PM | customer
Gravatar

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

just amazing ... great help to all .
3/20/2010 3:53 AM | sindhu
Gravatar

# 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
3/29/2010 5:23 PM | Pablo Aizpiri
Gravatar

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

Great post! Thanks a lot!

Petter, Sweden
4/6/2010 4:48 AM | Petter
Gravatar

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

GREAT Article and also explained very well.
Good JOB!
5/5/2010 1:04 PM | sarfraz
Gravatar

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

it's can use in MS SQL??
6/14/2010 11:06 PM | steven
Gravatar

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

Thank you for your post. It's very helpful. Great Job!
6/18/2010 1:18 PM | ShakaKhan
Gravatar

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

Great post helps me a lot to optimize queries.
Thanx for the job
7/16/2010 4:58 AM | Nanto
Gravatar

# 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>)
9/26/2010 10:52 AM | Mayank
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET