Bojan Petrovic

Querying data using the SQL Case statement

September 12, 2018 by

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 Where 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 Where clause. This might not be a good Case statement in SQL 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 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 Where clause. 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.


Bojan Petrovic

Bojan Petrovic

Bojan has a passion for working with computers, servers, and network technology, and electronics. He likes to stay updated with developments and the most recent technology. He currently works at ApexSQL LLC as a Support Engineer with a bachelor’s degree in IT Network and Electronics Technology.

He helps customers with any technical issues and does quality assurance (testing applications and preparing for production) for ApexSQL Complete, ApexSQL Refactor, and ApexSQL Search free add-ins.

View all posts by Bojan Petrovic
Bojan Petrovic

Latest posts by Bojan Petrovic (see all)

T-SQL

About Bojan Petrovic

Bojan has a passion for working with computers, servers, and network technology, and electronics. He likes to stay updated with developments and the most recent technology. He currently works at ApexSQL LLC as a Support Engineer with a bachelor’s degree in IT Network and Electronics Technology. He helps customers with any technical issues and does quality assurance (testing applications and preparing for production) for ApexSQL Complete, ApexSQL Refactor, and ApexSQL Search free add-ins. View all posts by Bojan Petrovic

269 Views