There are times in your T-SQL code when you need to return not a column, but rather a value that’s derived from a column or columns in your query. When that happens it’s time to use a CASE expression. What is a CASE expression? It’s simply a way of telling SQL Server, “I want you, for this column, to evaluate a list of conditions, and as soon as you hit one that turns out to be true return the value that is assigned to that set of conditions.”
For example, if we wanted to say that we wanted to classify our players into high, medium and low level characters, we could say, “All right. When the player level is 5 or 4, then it’s going to be high level. When it’s level 3 or 2, then it’s going to be medium level, and if they are a level 1 then it’s a low level character.” In the column then, we just see the words high level, medium level and low level.
Before you start using CASE expressions there are a few important thing you need to know about them. First and foremost CASE is an expression, not a statement. That means you can’t use CASE to control the flow of your query. You can’t say, “CASE, when player level equals one then do this other select statement.” It doesn’t work that way. All CASE will do, because it’s an expression, will return a value.
Also, you can nest CASE conditions, one after another, in a single expression. If we’re comparing this to IF, in SQL server all you have is IF and ELSE. If you want more levels than that, you have to start nesting them inside of each other. With CASE you can have: when A then 1, when B then 2, [00:02:00] when C then 3, when D then 4 and so on and so forth as far down as you need to go.
CASE, because it’s an expression, can be placed inside other functions. You can use a CASE expression inside a SUM function, for example, or AVG or other functions. As a related note, SUM put together with CASE makes for a really handy, cheap, easy PIVOT.
If you’re like me and you don’t want to learn the PIVOT syntax and every time you tried to learn you forgotten it then you’ll find yourself leaning on some CASE quite a bit.
CASE, because it’s an expression, can be used with SELECT, UPDATE, DELETE. It can be placed in a WHERE clause, in IN, in HAVING and GROUP BY and ORDER BY as well. It’s very flexible.
Technically it’s possible to put a CASE expression in a JOIN. Is it a good idea? Highly unlikely. There are alternatives to putting a CASE expression in a JOIN. In order for me to demonstrate those to you, I would have to mess up the screen a lot. I don’t want to do that. Just refer to the scripts that come with this video on replacing CASE and a JOIN with either OUTER JOINs or a set of UNION ALL queries.
Speaking of scripts, how about we go take a look at what CASE does.
In this first example we’ll look at just a simple CASE expression where we’ve got a PlayerName, and then we want to determine what they player’s role is in our party. If it’s Queen Agee then she’s the Leader. If it one of the other three known players, Countess Distincto, [00:04:00] The Hashmatcher or Volatire Perfmon, then we’re going to say that they are a Follower. If, for some reason, we have a player in there that isn’t a match with any of these values, it’s someone we haven’t accounted for, then we’ll just say they’re a Guest. It will be as simple as that. Right now we don’t have any players in our table that aren’t Guests. If we run this, we’ll see that we have Countess Distincto as a Follower, Queen Agee is a Leader, Hashmatcher is a Follower, Voltaire is a Follower as well.
This example is just a simple CASE expression that also contains a COUNT. What we want to do is find out what are the roles we have in our party right now. We have Leaders, Followers and Guests. We want to get a headcount of how many people we have in each of those roles.
The first column that we’re going to select will just be the name of the role here. That will be dependent on what the name of the player is as to what role they fill. We may not have Followers. We may not have Guests. It depends. We’ll have to run it and find out.
After that, we’ll do a count using that same expression. We’ll take the whole CASE expression and nest it inside the COUNT function. That should give us a headcount of how many Leaders, how many Followers and how many Guests.
Finally, because we’re doing an aggregate and a non aggregate column in the same query, we’ll have to make sure that we do the CASE expression up above that’s the party role down below in a GROUP BY. If we run this then we’ll see, we’ve got one Leader and we’ve got three Followers. That’s pretty much what we expected to find.[00:06:00]
This example will look at putting CASE in a WHERE clause. This expression that we’ve been working with for a while now to tell us if it’s a Leader, a Follower, or a Guest, the party role as we’ve been calling it, we’re going to take that same CASE expression and drop it down into the Where clause. Because a CASE expression will give you a single value back, that’s only going to be one half of the WHERE equation. We then need to specify what is is we want to look for since we’re trying to limit things in there WHERE clause.
In this case what we’re saying WHERE, and then we’ve got our entire party role expression that we’ve been using. What we want to do is limit the output to just the Followers, pretty simple. We’ll just say, equals Follower on the other side of the case expression. If we run this, what we should see are only the three Followers and not Queen Agee. There we go. We’ve got our three players because Queen Agee is a Leader. She doesn’t show up in the query. Because we don’t have any Guests in our player table, we don’t have anyone else besides just the three Followers we’ve been seeing so far.
In this example we’ll look at using the CASE expression to PIVOT results in our query. What we want to do is see how many times each player rolled each different value using a 6-sided die. Starting at the top we’ll select our PlayerName here, and then for each possible value of the 6-sided die, what we’ll do is we’ll take a SUM and then we’ll say CASE when they role the 1, then we want to say 1. We’ll put a little [00:08:00] tick mark in that box and say, “All right. That counts as 1.” Otherwise it doesn’t count as anything and whatever that adds up to be will be the number of times that they rolled a 1. We’ll repeat that logic for 2s, 3s, 4s, 5s, and 6s.
We’re getting this information from the Player table, we’re joining it to the PlayerRolls table, and then we’re just saying where it’s a 6-sided die. Again, because we’ve got aggregate functions in here, we have to make sure we GROUP BY. We’ll GROUP BY the PlayerName.
Go ahead and run that. A couple of interesting things pop out right away. #1, if higher values are good and lower values are bad then Hashmatcher’s pretty lucky. He’s rolled 111 6s and not nearly as many 1s. Other players look fairly well spread out. Perfmon is only slightly luckier than average. It looks like Queen Agee is about average across, as is Countess Distincto.
You can run this and get these results PIVOTed without having to know any sort of T-SQL about PIVOTs. You don’t have to learn PIVOT or UNPIVOT. You can just use the CASE expression to do this. If you’re going to have 20 columns, that means you’re going to have to do a lot of copy and pasting of this code to get each of the individual CASE expression values listed out, but, especially for shorter lists like this, or if you have 12 months, something that’s a tolerable amount of code to rewrite, this is a really handy and quick way of doing it.
This last example is how to use nested CASE expressions. [00:10:00] What we’re going to look for in this query is that danger level of the monsters in the monster table. The way that we’ll determine that is through a pair of CASE expressions, one nested inside the other.
We’ll select the MonsterName and then for our outer CASE expression, we’ll say, “Is the MonsterName LIKE Troll?” If it is, we’ll go into the inner CASE expression to figure out what kind of Troll. We’ll say, here if it’s a Blog Troll, and we’ll examine this based on the name, THEN it’s a 3 danger level. If it’s a Forum Troll THEN it’s a 2, if it’s not either of those, but it’s some other kind of Troll because we’re inside of that outer CASE expression at this statement, then we’ll say that there’s no threat. It’s a danger level 0. If it’s not a Troll of any kind, then we’ll give it a default value of 1.
Let’s go ahead and run this and see what we get. This is interesting because we have a Blog Troll, but that’s not its exact name. We’ve inadvertently tested this ELSE condition right here to see, “Okay, if it’s a troll but it’s not a Blog or a Forum Troll then it’s a 0?” Technically that’s true because it’s not a complete 100% match. This is a Blog Comment Troll. Up here we asked about Blog Trolls. That got a 0, but the Forum is an exact match, Forum, Forum. Everything else that’s not a Troll, as we expected, got a 1. We can test that 3 condition if we go back and say, “Blog Comment.” Run it again. It was just a mismatch in [00:12:00] the exact text we were looking for there. We fixed that up.
That’s a good way of doing things if, for example, you had a hierarchy of some kind like a country, a state and a city, or a product category or subcategory. You could do nested CASE expressions to handle all of that. It might end up looking a little cleaner it he long run. Personally I find that I almost never do nested CASE expressions because I can just flatten them all out. I’ll show you what that looks like down below here.
We’ve taken that same query and rewritten it getting rid of the inner CASE expression. We’ve reduced it back to one CASE expression. This can sometimes look a lot cleaner but it means writing a little bit more code. Also, when you read it and think through logically the results, you may find multiple lines that match up with that you’re trying to return. For example, if we have a Troll and it’s a Blog Troll we’ll say, “All right, then it’s 3.” If you come down a couple of lines and, where MonsterName LIKE Troll, “Okay I thought it was a Blog Troll and it was 3, but it’s still a Troll. Is it 3 or 0?”
These get evaluated in order from top to bottom. As soon as SQL server finds a match, as soon as it realizes, “Okay, this MonsterName begins with Troll and ends with Blog, I’m going to take my 3 and get out of there.” It will never make it down to the lines below. You don’t have to worry about things evaluated the second time and coming up with a second different result. It will bail out as soon as it finds something it evaluates to true.
The downside of doing things this way is that the MonsterName LIKE Troll, [00:14:00] up here we only had to write it once and then we got into the nested expression. Down here, we have to write it every time for each additional set of matching criteria. With this RIGHT MonsterName 4 and RIGHT MonsterName 5, these both have to have the same WHERE MonsterName LIKE Troll because we have to be complete in our requirements here.
As you’ll see, we can run this and it evaluates the exact same way. The result’s different because we had to fix this up a little bit, so we’ll change this to “Blog Comment,” and we should get our 3. Personally I prefer doing it this way as opposed to the nested CASE expressions because it’s really easy to do things syntactically wrong. You start writing multiple ENDs and it gets kind of crazy. This gets harder to read after a while.
If it’s not something where there’s a large hierarchy and it ends up, somehow, being cleaner, I almost always default to this style. I like having each condition being readable from top to bottom without knowing what CASE expression I am nested inside as I’m reading it. Chances are you’ll end up doing things this way too. Just so you know, there are two different ways of doing it. You can do it nested, or you can do everything inline like the second example. Do whatever works for you. It will evaluate the same way.
Now that we’ve seen what CASE expression can do, they look pretty powerful, but, seems like a lot of work writing these over and over again. What if there were a way to take CASE expressions and other formulas and not have to write them repeatedly? Maybe we could just [00:16:00] store them in the table somehow.
- 01. Prologue and Script Files
- 02. Thinking in Sets (5m)
- 03. Number and Date Tables (10m)
- 05. Computed Columns (10m)
- 06. Windowing Functions – Design (21m)
- 07. Windowing Functions – Performance (8m)
- 08. Replacing Cursors, Part 1 (6m)
- 09. Replacing Cursors, Part 2 (5m)
- 10. Replacing Cursors, Part 3 (7m)
- 11. Let the Adventure Begin! (9m)
- 12. About Doug (2m)