How to Use CASE in SQL

If you need to evaluate multiple conditional statements, the SQL CASE statement will do the job. To effectively harness CASE in SQL, grasping its structure and practical uses is key. I'll guide you through real query examples showcasing the power of this versatile statement. Here’s what you need to know to use CASE like a pro.

Why is CASE so important in SQL? If you’re analyzing or manipulating data, you’ll often want to define rules based on certain conditions, e.g. if an employee’s performance is above average, give them a 10% raise; if it is outstanding, give them a 15% raise; otherwise, give them a 5% raise.

To handle situations where you need to evaluate many conditional statements together and return results depending on which statement is true, SQL offers the CASE statement.

The SQL CASE statement is one of the most useful conditional constructs available and has a lot of applications for analyzing data with SQL. It is one of those things that every data analyst should have in their arsenal.

To practice using CASE statement after reading this article, I recommend our interactive course Creating Basic SQL Reports. It contains almost 100 exercises and is focused on using CASE in different practical SQL problems.

What Is the CASE Statement?

In SQL, the CASE statement returns results based on the evaluation of certain conditions. It is quite versatile and can be used in different constructs. It's a simple yet powerful way to make your data adapt and respond to various scenarios, making your database queries more dynamic and insightful.

Before I go into details on how CASE works, take a look at the syntax of the CASE statement:

CASE
WHEN <condition> THEN <value>,
WHEN <other condition> THEN <value>
ELSE <value>
END AS <column name>

Let’s look at a practical example of a simple CASE statement.

Here is the order_summary table:

Now say you are analyst in an ecommerce firm. You want to analyze orders based on their order value and divide them into buckets (very low, low, medium, high, and very high) according to their order value.

The CASE statement can help you achieve this. Here’s the query you’d write:

SELECT  order_id,
	  CASE 
	  WHEN order_value <= 50 THEN 'Very Low'
  WHEN order_value > 50 AND order_value <= 200 THEN 'Low'
  WHEN order_value > 200 AND order_value <= 500 THEN 'Medium'
  WHEN order_value > 500 AND order_value <= 1000 THEN 'High'
  ELSE 'Very High' 
  END AS order_category
FROM    order_summary;

And here are the results you’d get:

Alternatively, you can also use the query given below:

SELECT order_id,
 CASE
 WHEN order_value <= 50 THEN 'Very Low'
 WHEN order_value <= 200 THEN 'Low'
 WHEN order_value <= 500 THEN 'Medium'
 WHEN order_value <= 1000 THEN 'High'
 ELSE 'Very High'
END AS order_category
FROM order_summary;

This will give you exactly the same result, as CASE stops evaluating a value once it meets the criteria in WHEN. The CASE statement is essentially logic in action, directing data based on set conditions.

Now, let me break down these queries.

The first keyword is SELECT, which specifies the columns you want to return. In our case, these columns were order_id and order_category, which we used as an alias for the CASE statement (CASE...END AS order_category).

The CASE statement begins with the keyword CASE. It is followed by the keyword WHEN, after which we specify a condition to evaluate (order_value <= 50). This is immediately followed by THEN and the return value if the condition is true (‘Very Low’).

For example, take the first statement:

CASE WHEN order_value <= 50 THEN 'Very Low'

In this statement, when the order value is less than or equal to $50, ‘Very Low’ is returned as a value in the order_category column. In other words, we classify all the orders with value less than $50 or equal to $50 in the “Very Low” category.

If this condition isn’t true (the value is over $50), the query checks to see if the value is over $200. If it is under $200 but over $50, then “Low” is returned as the value in the order_category column. If the value is over $200, the query skips to the next WHEN clause, and so on.

If none of the conditions evaluates to true, then the value specified in ELSE is returned. Thus, the CASE statement adds logic to your SELECT statement.

If you are new to SQL and want to understand how to write these kinds of queries, I recommend the SQL from A to Z track from LearnSQL.com. It starts with the basics of SQL and databases, and then guides you all the way up to more sophisticated queries and functions. It’s a great way to get started on your SQL journey.

Using CASE with the GROUP BY Clause

If you’re analyzing a lot of orders, aggregation would come in handy on queries like these. Aggregation means grouping similar records and then using a metric based on the grouped values to understand the features of that group. In SQL, the GROUP BY clause is your entry into the world of aggregate statistics. (For a more detailed understanding of GROUP BY, check out this article.)

For now, let’s just see how GROUP BY and CASE work together. Here’s an updated version of our previous query:

SELECT  CASE 
	  WHEN order_value <= 50 THEN 'Very Low'
  WHEN order_value > 50 AND order_value <= 200 THEN 'Low'
  WHEN order_value > 200 AND order_value <= 500 THEN 'Medium'
  WHEN order_value > 500 AND order_value <= 1000 THEN 'High'
  ELSE 'Very High' 
  END AS order_category,
  COUNT(order_id)
FROM    order_summary
GROUP BY 1;

And the new output:

Here, we use COUNT as the aggregate function. This is how it works. The GROUP BY clause aggregates all the records by the values returned in the first column of the SELECT. In our case, this is order_category.

Then, for each different value of order_category, COUNT(order_id) will calculate the total number of orders belonging to the corresponding category. The CASE statement helps decide which category to assign for each order. In our data, we have a total of 1 order in the ‘High’ category (order_value between 500 and 1000), 2 orders in ‘Medium’ (order_value between 200 and 500) and 1 orderin the ‘Very High’ category (order_value greater than 1000).

More info on COUNT can be found here.

In all the above examples, the CASE statement has been used in the SELECT part of the query. However, this clause is quite versatile and can be used for returning condition-based results in other parts of the query.

So now that you have an idea of what the CASE statement is, let’s see some other ways to use it.

Using CASE in the ORDER BY Clause

The ORDER BY clause is used to sort query results in a given order. For instance, you might want to sort the number of orders placed by each customer on the basis of customer_name. Here’s the query you’d write:

SELECT   customer_name,
   COUNT(order_id)
FROM 	   order_summary
GROUP BY customer_name
ORDER BY customer_name;

And the output is:

Here, the query sorts the results in ascending alphabetical order (because you’re ordering by a text value). Unless you specify otherwise, ORDER BY will always use ascending (i.e. A-Z, 1-10) order. You can place the DESC keyword after the column name clause to sort the results in descending (Z-A, 10-1) order: ORDER BY customer_name DESC.

Suppose you want to sort records by order_id in ascending order. However, you want to show orders of over 120 items first. In other words, you will first sort by item quantity (wherever the quantity is greater than 120) and then by order ID. This will require a conditional evaluation in the ORDER BY clause:

Query:
SELECT
  customer_name,
  order_id,
  order_value,
  quantity
FROM
  order_summary
ORDER BY
  CASE WHEN quantity > 120 THEN quantity END, order_id;

Here is the output:

In this query, first we get the customer_name, order_id, order_value and quantity columns from the table. In ordering the rows, this query first gets rows where the quantity is greater than 120. (In this case, the quantity is 123.) Since we have no other rows that meet that criteria, the rest of the rows are ordered by order_id.

Here are more resources on the ORDER BY clause:

• What Does ORDER BY Do?
• A Detailed Guide to SQL ORDER BY
• How to Order by Count in SQL

Using CASE in the WHERE Clause

The WHERE clause is used to filter records from the query results based on declared conditions. For example, if your company wants to waive shipping fees for orders over $100, you may first want to see how many orders will qualify and analyze the impact. The following WHERE clause will only count IDs for orders over $100:

SELECT  COUNT(order_id)
FROM    order_summary
WHERE   order_value > 100;

And the result:

Based on the result, you’ll assume around 4 orders will be impacted by this. Of course, this is the first step of your analysis; you’d likely want to do many more detailed analyses to quantify the impact.

Now let me take you through an example of using the WHERE clause with CASE. Take a look at the influencer_list table:

Let’s say that your business uses various influencers to promote your brands. You want to see all the influencers whose YouTube channel or Facebook account directly uses your name (‘BrandX’).

Each influencer has one type of channel/account. Here’s how you’d find out which ones mention BrandX:

SELECT DISTINCT influencer_name
FROM influencer_list
WHERE CASE WHEN influencer_channel = 'facebook' THEN fb_channel
	     WHEN influencer_channel = 'youtube' THEN youtube_channel
	     END LIKE '%brandX%';

Here’s the result:

The above query will return all rows where either youtube_channel or fb_channel has ‘brandX’ in it. How do we do this? Well, you know how WHERE and CASE WHEN work together. The new element here is LIKE '%brandX%'. All that does is tell the query to return the influencer channels that contain ‘BrandX’ in their name; LIKE is used to match the column value to the pattern, and the percent sign (%) indicates that any number of characters can come before or after ‘BrandX (which is why the % is at both ends of BrandX).

Here are more resources on the WHERE clause:

• The Complete Guide to the SQL WHERE Clause
• How to Write a WHERE Clause in SQL
• What Is the Difference Between WHERE and ON in SQL JOINs?

Using CASE in the HAVING Clause

The HAVING clause is used with the GROUP BY clause to filter the groups being displayed. For instance, if you wanted to see records from the influencer_list table where total_views over the influencer’s lifetime are greater than a million, you’d write:

SELECT      influencer_name,
	   SUM(total_views)
FROM     influencer_list
GROUP BY influencer_name
HAVING   SUM(total_views) > 200000;

And this is what you’d get:

You can also use CASE with the HAVING clause. Say you want to get a list of influencers whose total views are greater than 100 for YouTube or greater than 400,000 for Facebook.

SELECT      influencer_name,
	   influencer_channel,
	   SUM(total_views)
FROM     influencer_list
GROUP BY influencer_name,
	   influencer_channel
HAVING   CASE WHEN influencer_channel = 'youtube' 
        THEN SUM(total_views) > 100
  WHEN influencer_channel = 'facebook' 
  THEN SUM(total_views) > 400000
   END;

And the result:

This query first sums up the total views by influencer_name and influencer_channel. In the HAVING clause, we then filter only those groups that have more than 100 views for YouTube and more than 400,000 views for Facebook. Notice that Michael, who has 242,322 Facebook views does not feature in the output; his total is less than 400,000.

Here are more resources on the HAVING clause:

• SQL HAVING Tutorial
• HAVING vs. WHERE in SQL: What You Should Know

Using CASE in an UPDATE Statement

You can also use CASE in an UPDATE statement. The SQL UPDATE statement is used to change values in an existing table.

Imagine that you want to update the influencer_channel values in our current dataset by changing the channels to a two letter code: ‘youtube’ has to be changed to ‘yt’ and ‘facebook’ has to be changed to ‘fb’.

UPDATE influencer_list
SET     influencer_channel = CASE influencer_channel 
 			  WHEN 'youtube' THEN 'yt'
			  WHEN 'facebook' THEN 'fb'
			  ELSE 'invalid value'
			  END;

This is how the influencer_list table will look after the update:

You will notice that ‘youtube’ has been replaced with ‘yt’ and ‘facebook’ has been replaced with ‘fb’ in the influencer_channel column.

You can also use CASE to delete or insert rows in your tables. Read this article on using CASE with data-modifying statements for more details.

CASE Statement Limits

The CASE statement in SQL is great at conditional logic within queries, but it has its limits. 

Sequential Evaluation in CASE

CASE in SQL works on a first-match basis. When you have multiple conditions, SQL evaluates them in the order you've specified until it finds a match. Once a match is found, it stops checking further conditions.

This sequential operation requires careful planning of your conditions to ensure accurate and expected results. Misordering can lead to unexpected outcomes, so it's essential to understand how SQL processes these statements to make your queries efficient and reliable.

Remember that it's a best practice to include an ELSE block in your CASE statements. This ensures a default outcome for any unmet conditions, avoiding unexpected NULL results and maintaining consistent, predictable returns in your SQL queries. This enhances the robustness and clarity of your database scripts.

CASE Statement Flow Control

It's important to understand that CASE is not the best option for controlling the execution flow of stored procedures or functions. This means that while CASE is great for making decisions within a single query, it won't dictate the broader sequence of operations in your database scripts. It’s a much better idea to use an IF statement in this case. Understanding this helps in planning more complex database tasks and ensuring that your SQL code is structured correctly.

Ready to Use CASE in SQL Queries?

After all these examples, I am sure that you have a better idea of how CASE works in SQL and the statement’s various potential applications. So, it is time you put your learning into action! Reading about SQL will surely help you learn, but if you want to become an expert, your mantra is “Practice!”.

I’d also recommend a good SQL practice course. The LearnSQL.com practice course uses practical examples and use cases, and you don’t need to set anything up to start an Internet connection and a browser is enough.

The more queries you write, the better you will become at CASE and other SQL commands. Why wait? Get started now!