Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.


Free Beginners SQL Course!

Can I have a CASE Statement in the WHERE Clause?




I’m commonly asked whether whether I can have a CASE Statement in the WHERE Clause. There are a number of examples using the CASE WHEN construct in SQL, such as the SELECT columns or in ORDER BY clauses, but we tend to forget CASE can be used wherever an expression is expected.

Where Can I use a CASE Statement?

You can use a CASE Statement anywhere a valid expression is used within the SELECT statement such as the WHERE clause’s filter criteria.

To learn more about WHERE, check out our article SQL WHERE – Guide and Examples

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
WHEN PersonType = 'VC' THEN 1
WHEN PersonType = 'IN' THEN 1

The idea here is to test PersonType for either VC or IN. If it matches the corresponding CASE WHEN return a 1. The filter matches and the row is included in the result.

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

Which one one would you use?

In this case I would tend to use the IN operator, it is succinct.

That said, don’t discount CASE! I find it extremely useful when working with more complex logic. In these situation, if you format the statement correctly, the code is much easier to read than nested Boolean operators, and it something you just can’t do with IN alone.

Here are some Related Posts to check out:

15 responses to “Can I have a CASE Statement in the WHERE Clause?”
  1. Jeremy Carroll

    I added one more option using the table value constructor for fun. It doesn't perform badly as is, but begs for a covering index.

    SELECT FirstName, LastName, PersonType
    FROM Person.Person AS p
    INNER JOIN (VALUES ('VC'),('IN')) AS x(pType)
    ON p.PersonType = x.pType;

    Kris – Thank you for your contributions to my education and to the SQL Server Community.

  2. John

    Appreciate the objective discussion.

  3. […] 6 mar. 2022 … Is it OK to have a CASE WHEN Statement in the WHERE Clause? You can! A CASE WHEN in SQL is a great way to add logic to your query. >>> […]

  4. John

    Great content! Thanks for sharing, it’s very valuable & information, especially the CASE in a WHERE clause.

  5. Yesh

    this was perfect…thank you thank you. Had a bunch of nested data with left joins and needed an easy way to eliminate rows where a specific value was null

  6. Vishal Sharma

    it’s so good, worked like a charm!!

  7. siraj kalesha

    it useful and applied thanks

  8. Jason

    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.


  9. peter

    Does using CASE statement cause table scan where IN or OR will not (granted the column in indexed). Thx.

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

  10. Aaron

    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

    1. That is a good trick. :)

  11. Hi, I’m more apt to use a CASE in the column list or order by. I don’t think I would use it in a WHERE, even though it is legal.

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

  12. Andy

    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?


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SQL Server