A very common question I get asked is whether I can have a CASE Statement in the WHERE Clause. There are a number of examples of CASE being used in SELECT columns, or in ORDER BY that we tend to forget CASE can be used wherever an expression is expected.

Where Can I use a CASE Statement?

According to MS SQL Docs, a CASE statement can be used throughout the SELECT statement.


CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

Microsoft SQL Docs, CASE (Transact-SQL)

Example Query

Suppose we want to get all people from the Persons table whose persontype is either VC or IN. To do this with CASE you could write:

SELECT FirstName, LastName, PersonType
FROM Person.Person
WHERE 1 = CASE
WHEN PersonType = 'VC' THEN 1
WHEN PersonType = 'IN' THEN 1
ELSE 0
END

In this example CASE returns a one if PersonType matches. Then, since 1 = 1 is true, the row is returned.

OK, so now you can see that you can use CASE statement within a WHERE clause. Does that mean you should?

Personally I think it comes down to readability. In this case, the logic is pretty simple. Here I think it makes sense to stick with the basics. Just use Boolean OR or the IN operator.

Here is the example with OR

SELECT FirstName, LastName, PersonType
FROM Person.Person
WHERE PersonType = 'VC' or PersonType = 'IN'

Here is the same example with SQL IN

SELECT FirstName, LastName, PersonType
FROM Person.Person
WHERE PersonType in ('VC','IN')

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Hi Kris,

    You answered my initial question as to why would I use a CASE in your example as I’d of generally used the IN, so could you provide an example of where you would definitely prefer to use a CASE?

    Regards
    Andy

    • Kris,

      Up to now, I had only used CASE in my select clause. I just developed a query, though, which checked the timestamp in order to deliver the results of same day versus previous day. The reason for the need is that we run a 2 shift operation and 2nd shift runs until 1AM. We wanted a query where the user would not have to change anything, and would instead just run the query as is to get their day’s results. By using CASE in the WHERE clause, we were able to accomplish this.

  • I used it for a report query when a field was returning ‘Y’ or null and I was comparing it to a boolean parameter:

    WHERE @OnPublishedProgram = CASE
    WHEN On_Published_Program = ‘Y’ THEN 1
    ELSE 0
    END

    • Hi, The optimizer will try to use an index. In the simple case I provided it verified it did use a clustered index scan, but I imagine as your CASE gets more complicated there’s less a chance, especially one with several columns to evaluate within the expressions.

  • I found this post while trying to figure out the correct syntax for CASE WHEN in a WHERE clause and it helped – thank you.

    >> OK, so now you can see that you can use CASE statement within a WHERE clause. Does that mean you should?

    I have a Table Function which is split into 4 sections with IF | ELSE statements where each section contains a variant of the same query comprising SELECT, FROM, WHERE, GROUP BY, ORDER BY & OFFSET FETCH and where each differs only by the conditions in the WHERE clause. This is to allow the client to define the extent of the data that’s returned, ie: {booked events only, non-booked events only or all} and {future events only, past events only or all}.

    1. WHERE [TFP].[Projects].[UserID] IN (SELECT [UserID] FROM @TempTable_ColleagueUserIDs) AND [TFP].[ProposalRevs].[Booked] > 0 AND [TFP].[Projects].[EventDate] >= CAST(GETDATE() AS DATE)

    2. WHERE [TFP].[Projects].[UserID] IN (SELECT [UserID] FROM @TempTable_ColleagueUserIDs) AND [TFP].[ProposalRevs].Booked > 0

    3. WHERE [TFP].[Projects].[UserID] IN (SELECT [UserID] FROM @TempTable_ColleagueUserIDs) AND [TFP].[Projects].EventDate >= CAST(GETDATE() AS DATE)

    4. WHERE [TFP].[Projects].[UserID] IN (SELECT [UserID] FROM @TempTable_ColleagueUserIDs)

    I could greatly reduce the length of the overall query by removing the IF | ELSE blocks and inserting CASE WHEN into the WHERE clause. It would also mean that any common change I need to make could be done in just 1 query rather than 4.

    I’m curious if there’s any reason I shouldn’t do this but, more than that, validation as to whether having a single Table Function return data in 4 different ways is OK, ie: good practice, or if it’s perhaps better practice to have 4 distinct Table Functions.

    Thank you for any insight.

    Jason.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

    Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

    But it doesn't have to be this way.

    I'm Putting together a free email course to help you get started learning SQL Server.

    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b6728":{"name":"Main Accent","parent":-1},"03296":{"name":"Accent Low Opacity","parent":"b6728"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"b6728":{"val":"var(--tcb-skin-color-0)"},"03296":{"val":"rgba(17, 72, 95, 0.5)","hsl_parent_dependency":{"h":198,"l":0.22,"s":0.7}}},"gradients":[]},"original":{"colors":{"b6728":{"val":"rgb(47, 138, 229)","hsl":{"h":210,"s":0.77,"l":0.54,"a":1}},"03296":{"val":"rgba(47, 138, 229, 0.5)","hsl_parent_dependency":{"h":210,"s":0.77,"l":0.54,"a":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__
    Name*
    Email*
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"dffbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"dffbe":{"val":"var(--tcb-color-4)"}},"gradients":[]},"original":{"colors":{"dffbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
    Sign Up
    >