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
WHERE 1 = CASE
WHEN PersonType = 'VC' THEN 1
WHEN PersonType = 'IN' THEN 1
ELSE 0
END
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.
Leave a Reply