The Case statement in SQL is mostly used in a case with equality expressions. The SQL Case statement is usually inside of a Select list to alter the output. What it does is evaluates a list of conditions and returns one of the multiple possible result expressions.

For instance, let’s see how we can reference the “AdventureWorks2012” database and show an example of a SQL Case statement.

We are going to take the “Gender” field which is only a character field. So, if we go to our sample database, tables, inside the “Employee” table there’s a column called “Gender” which is nchar data type. Right above is the “MaritalStatus” which is also nchar data type, meaning that those two have only one character:

So, if we want to output this to change the display of marital status and gender categories to make them more understandable consider the following query using a SQL Case statement:

This is a great equality expression case statement in SQL. What it basically means is that we can do Case followed by the field name and then we can have all case expressions. In this particular case, whenever “Gender” equals “F” then it’s going to output “Female” and when it equals “M” then it’s going to output “Male”. Also, if it’s neither of those it’s going to output “Unknown”. Furthermore, we’re aliasing the name of the column as “GenderDescription”. Another way to do aliases is to put the name of the alias first followed by equals SQL Case on “MaritalStatus” as in the example above.

So, if we now execute our query with this SQL Case statement it will fetch that information and output it appropriately:

It’s not seen in this example because all the fields match arguments but the Case statement in SQL supports an optional Else argument. This means that you’d have seen the “Unknown” output as well if the character was anything else than stated in the When clause.

That’s one use of the SQL Case statement (equality version of it). There’s also a searched expression version of it that allow us to work on a range. The following query is based on the price range for a product:

Now, instead of doing SQL case statement followed by a column name we just do Case, When, and then column name with the output range and what we want to print out. The above query returns the “Price Range” as a text comment based on the price range for a product:

Now, let’s see a couple of quick examples when a SQL Case statement can be also used because a lot of times and the most common place you’re going to see a Case statement in SQL is in a Select list to do things like we did above to modify and work with the output. But a lot of times and in some more advanced and complex stuff like stored procedures, you’ll see SQL Case statement in places like Order by and/or SQL Where Case clause.

While this can be a little outside of the realm or outside the comfort level of the basic query class, it’s good to get familiar with those as well because you’re bound to come across at some day and it’s good to know why they’re there and see the power of Case statement in SQL.

Here’s an example with the Order by. Let’s say we want to order the results of our products. Run a quick Select statement to retrieve everything from the table:

Note that we are interested in the “MakeFlag” column which is a Flag data type and contains information if the product is purchased (0) or is manufactured in-house (1):

Now, let’s say we want to order this results list and show up the products that were purchased first and then the ones manufactured in-house. This is easily done just by adding the SQL Case with “MakeFlag” when it’s 0, then order by “ProductID” descending:

Everything up to row 265 is purchased and the rest is manufactured in-house. And that is how SQL Case statement is used in an Order by clause:

Here’s another example with the SQL Where Case. This might not be a good SQL Where Case statement because the query below does not make any sense, right?

You’re probably thinking we could just say Select everything from “Product”, where “ListPrice” is less than 100. Yes, but bear with me. The Where clause says 1 equals case when the price is less than a 100, output one, else 0 end. What we’re actually doing here is we’re saying when 1 equals 1, return the row and when 1 equals 0, do not return the row. In this particular case, when the price is less than a 100 it will return the row and when it’s greater than a 100 it will not return anything.

If we do a quick Select of everything, we get 504 rows returned:

If we include the Where clause with the SQL Where Case in it, then we get 290 rows returned:

This still might not look like something useful right off the bat, but you’ll most likely come across a situation where you must make a decision in the SQL Where Case. Well, the SQL Case statement is a great start. Here’s a good analogy. In databases a common issue is what value do you use to represent a missing value AKA Null value. Here we can’t do equalities because we can’t say things like “where a field equals Null” because Null does not equal 0. This is actually a perfect example when to use the Case statement in SQL.

I hope you found this article on the Case statement in SQL helpful and I thank you for reading.

• Author
• Recent Posts

Bojan Petrovic

Bojan aka “Boksi”, an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology, is a software analyst with experience in quality assurance, software support, product evangelism, and user engagement.

He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.

Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.

See more about Bojan at LinkedIn

View all posts by Bojan Petrovic

Latest posts by Bojan Petrovic (see all)

• Visual Studio Code for MySQL and MariaDB development - August 13, 2020
• SQL UPDATE syntax explained - July 10, 2020
• CREATE VIEW SQL: Working with indexed views in SQL Server - March 24, 2020