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.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
