Can I have a CASE Statement in the WHERE Clause?

A common question I get ask is whether I can have a CASE Statement in the WHERE Clause. There are so many 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 CASE 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 basis. 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 IN

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

The post Can I have a CASE Statement in the WHERE Clause? appeared first on Essential SQL.