SQL Server CASE Statement: A How-To Guide

The CASE expression is a very handy tool you should know when querying data using T-SQL in a Microsoft SQL Server database.

In this very brief tutorial, we’ll break down the CASE expression and show you exactly how to write it and how it can make your querying life easier.

We will cover the following topics:

1. What is the CASE expression?
2. The “Simple” form of the CASE expression
3. The “Searched” form of the CASE expression
4. Tips, tricks, and links

And so it begins…

1. What is the CASE expression?

The CASE expression is used to return a single value based on the result of a condition. It basically replaces a value with some other value you specify. 

The CASE expression is great if you want to return a user-defined value instead of data that’s seen in your table.

That’s not a very clear definition, I know. The easiest way to understand CASE expressions is to just look at some examples.

But first, understand there are two forms of the CASE expression: Simple and Searched. We will go over examples of each.

2. The “Simple” form of the CASE expression

As I said earlier, the easiest way to understand the different CASE expressions is to look at some example.

Let’s take the following Employees table as an example:

Notice the Seniority column. The information in this column isn’t exactly helpful, is it? Looking at the data, I don’t really know what those numbers mean.

But internally, within our organization, we know the numbers correspond to the following roles:

3 – QA Engineer

4 – Team Lead

5 – Intermediate Developer

6 – Senior Developer

Maybe when this table was created, we decided it was much easier to just use a number instead of typing in the long text every time we want to add or change an Employee’s role.

But what if we wanted to change our query above to display the actual role instead of the number? For example, for our first employee Mark Thompson, instead of showing his seniority as being 6, we display it as ‘Senior Developer‘?

Using the CASE expression, we can do that.

The first step is to specify the column you want to use with the CASE expression, like this:

In other words, the column we want to replace values for is the Seniority column, so that’s the column we specify next to the CASE keyword.

From there, we need to specify the different values that Seniority can be, and what values we want to replace those values with.

We know the Seniority will be either 3, 4, 5 or 6. And we know what text we basically want to replace those values with, respectively.

So the way we do that is by using the following pattern:

WHEN <column value> THEN <replacement value>

This pattern will be repeated for all column values.

Here is what I mean:

So for example, when we see the number 3 in the Seniority column of a row, then we replace it with the phrase ‘QA Engineer‘

When we see the number 4 in the Seniority column of a row, then we replace it with the phrase ‘Team Lead‘.

So on and so forth!

(By the way, the replacement value doesn’t need to be a character string. You could use anything you want there, like a different number or a scalar function. It just needs to return a scalar value, meaning it returns one thing)

After all your WHEN clauses have been outlined, you can choose to put a default ELSE clause. This is used if a value in the Seniority column is not accounted for in your list of WHEN clauses.

The syntax looks like this:

The ELSE clause is optional, but it’s a good idea to include it. If it’s not there, values not accounted for will just have NULL for the replacement value.

Last but not least, we basically need to end the CASE expression. We do this by using the keyword END, like so:

Nice, now let’s run the query and see what we get:

It looks like our Seniority column values were all replaced correctly!

You should use an alias for your CASE column

You probably notice something odd about our result set. Why isn’t the column called “Seniority“?

When you use the CASE expression, you are essentially creating a new derived column. Sure, it’s based off an actual column, but it isn’t that actual column anymore. It’s a derived column.

You should give this new derived column an alias. You would outline the alias directly after the END keyword, like so:

The keyword “AS“ is optional. We could have just said “END ‘Seniority\Role’“. I like to keep the word “AS” in there because I think it makes the code a bit more readable.

And the alias can be anything you want it to be. You could put ‘Spaghetti’ as the alias if you want to.

To demonstrate the use of the ELSE, let’s add another Employee with a Seniority not accounted for yet in our list of WHEN clauses: 

There you have it.

3. The “Searched” form of the CASE expression

If you look back to our example of the Simple form of the CASE expression, you’ll see that it is basically equality based.

For example, all the WHEN clauses are basically saying the following:

If Seniority is equal to 3, replace it with ‘QA Engineer‘

If Seniority is equal to 4, replace it with ‘Team Lead‘

So on and so forth.

What if we wanted to do more of a comparison based or range based check? Like if we want to ask if an amount is greater than 200, or if a date is between June 1st and June 30th?

Let’s think about an example. Here is a table called BookCollection:

We can create a new user-defined column called BookSize. This column will tell us if the book is “short“, “average“, or “long“.

A “short” book has less than 200 pages. An “average” book has between 200 and 300 pages, and a “long” book as over 300 pages.

Let’s think about how we would need to write our WHEN clauses if we were using the Simple CASE form. For “short” books, we would basically need 200 WHEN clauses! Like this:

Then we would need 100 WHEN clauses for “average” books. Then I suppose we could use the ELSE clause for books greater than 300 pages.

So, does that sound like too much work? Duh!

Wouldn’t it be easier if we did something like this:

Yep, I think that’s easier!

The code in that screenshot is not complete. There are a few things we need to do before we have a fully-working query.

First, when you use the Searched form of the CASE expression, you don’t specify a column at the top of the CASE expression. I’m talking about this column:

You take it out, like this:

Cool. As for the rest of the query, you still need to END the CASE expression like we did before, like this:

And that’s it! Let’s see it in action:

Nice.

The first WHEN clause to evaluate to TRUE gets ran

You might notice how SQL works through the different WHEN clauses. Basically, when working through each row in the result set, SQL will look at each WHEN clause from top to bottom and stop at the first WHEN clause that evaluates to TRUE. That first TRUE WHEN clause get’s it’s THEN clause ran. All other WHEN clauses get skipped.

To demonstrate that idea, let’s add another WHEN clause for books that are ‘Very Short‘:

Let’s think about the first book, As a Man Thinketh. It has 45 pages, which is less than 50 and less than 200.

So both those WHEN clauses in the red box are true, but the first WHEN clause that evaluates to TRUE gets ran, which is why we get ‘Very Short‘ in the result set.

The comparison of PageCount < 200 isn’t first, is it?

But see what happens if we flip the two WHEN clauses:

Notice in the result set we have ‘Short‘ now. Again, it’s because the first WHEN clause to evaluate to TRUE is PageCount < 200.

Moral of the story: You need to make sure your WHEN clauses are in the correct order.

If you think about, we basically just created a situation where the “Very Short” condition will never be met. So do yourself a favor and double-check the order of your WHEN clauses!

4. Tips, tricks, and links

Here are some tips and tricks to remember about the CASE expression:

Tip # 1: The CASE expression can be used anywhere a scalar (aka “single”) value is expected

The CASE expression can be used not only in the SELECT list, but also in the WHERE clause, HAVING clause, ORDER BY clause, or even in something called a CHECK constraint. These are all places where a single value is expected.

This whole time I have been showing you examples of using the CASE expression in the SELECT list, but understand it can be used other places, too. Most of the time, you’re going to use it in the SELECT list, though.

Tip # 2: Remember, the first WHEN clause to evaluate to TRUE will have it’s THEN clause ran

Make sure the order of your WHEN clauses is correct!

Links

There is an extremely helpful book that introduces you to many T-SQL topics, including the CASE expression, that you should get your hands on. It is called “T-SQL Fundamentals” by Itzik Ben-Gan. This is one of only a few books I own that helped me understand many SQL Server topics. You definitely won’t regret owning this book, trust me. Get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

Do you know how the IF…ELSE decision structure works? Click the link to find out!

You should definitely be familiar with the IF…ELSE decision structure. This tool is sort-of related to the CASE expression in that you are evaluating some condition to be either TRUE or FALSE.

Also, there is another great decision structure you should be familiar with to make your query-writing life simple and easy:

SQL IF EXISTS Decision Structure: Explained with Examples

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!