CASE Statements in SQL: Complete Beginners Guide

One of the best ways to land a career in a field like data analytics or data science is to master relational databases and structured query language (SQL). By mastering SQL, you’ll be able to manage databases by creating records, reading data, updating records, and deleting records. These operations are known as CRUD operations and fall under the umbrella of data manipulation language (DML). 

When handling data for CRUD operations, you’ll run into situations where you’ll need to process data that meets certain conditions. Conditions can be evaluated using the CASE statement in SQL. In this article, we’ll dive into CASE statements in SQL so you can see exactly how the CASE expression is used in each of the CRUD operations. We’ll cover the following:

1. What is a CASE statement in SQL?
2. How do you write a CASE statement in SQL?
3. Examples of the CASE statement in SQL
4. Summary and next steps

If you’re new to SQL, you can learn how to set up Microsoft SQL Server for free along with all of the basic commands in this introduction to SQL article.

1. What is a CASE statement in SQL?

In SQL, CASE statements allow you to evaluate conditions and return a value once a condition is true. CASE statements always begin with the CASE keyword and end with the END keyword. If no conditions are true, you can use the ELSE clause to return a final value. If the ELSE clause is not used in the CASE statement in SQL, a NULL returns. CASE statements in SQL can contain up to 255 conditions. 

Using CASE statements in SQL can be very handy since they allow you to transform and transpose output. For example imagine you have a list of states and want to transform them into abbreviations. You can use CASE to return CA when the state equals California.

Before we start: Prepare example data 

As we explore the CASE statement in SQL, we’ll need some data to play with. Prepare data for the examples by creating a temporary table and inserting some rows. In SQL server, tables that start with # are temporary tables.

create table #temp_table (
	personID int IDENTITY(1,1) PRIMARY KEY
	, [state] varchar(50)
	, raceEthnicityCode int
	, lastName varchar(50)
	, firstname varchar(50)
	, income int
) 

insert into #temp_table ([state], raceEthnicityCode, lastName, firstname, income)
values ('Minnesota', 6, 'Johnson', 'Oliver', 100000)
insert into #temp_table ([state], raceEthnicityCode, lastName, firstname, income)
values ('Minnesota', 1, 'Gasga', 'Luis', 102000)
insert into #temp_table ([state], raceEthnicityCode, lastName, firstname, income)
values ('California', 2, 'Anderson', 'Kristen', 200000)
insert into #temp_table ([state], raceEthnicityCode, lastName, firstname, income)
values ('Kentucky', 2, 'Anderson', 'Jenny', 65000)
insert into #temp_table ([state], raceEthnicityCode, lastName, firstname, income)
values ('Kentucky', 7, 'Clinton', 'Lamar', 82000)

select * from #temp_table

The output of our temp table will look like this:

2. How do you write a CASE statement in SQL?

The syntax for the CASE statement in SQL is fairly straightforward, but the use-cases can get complicated when applying logic, functions, or nested expressions. The CASE keyword can be used in statements such as SELECT, UPDATE, DELETE and SET, and in clauses IN, WHERE, ORDER BY, and HAVING. This is the basic syntax:

CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
WHEN condition_n THEN result_n…
ELSE result
END

Let’s look at the parameters within the syntax. Notice CASE Statements in SQL use the keywords WHEN and THEN to evaluate the condition and return a result. After the database finds the first true condition, the corresponding result is returned, and then the database stops evaluating the case statement. Giving the CASE statement an alias is optional, but should be done when returning a result in the select statement or the column name is null. 

3. Examples of the CASE statement in SQL

Now that we’ve reviewed syntax and understand the parameters, let’s work through some examples that show how to use a CASE statement in SQL. We’ll start with simple SELECT statements and end with some complex examples that show how to use CASE for updating and deleting records. 

We’ll work through the following examples:

• Simple CASE statements in SQL
• Searched CASE statements in SQL
• Filtering using CASE Statements in SQL
• Aggregating using CASE statements in SQL
• Nested CASE statements in SQL
• Multiple matches using CASE statements in SQL
• Updating records using CASE statements in SQL
• Deleting records using CASE statements in SQL

Simple CASE statements in SQL

The simple CASE expression evaluates conditions of the expression to determine the result. Let’s write a SELECT statement that uses a CASE expression on the [state] column that transforms the state into the abbreviation. 

SELECT personID, [state],
CASE [state]
	WHEN 'Minnesota' THEN 'MN'
	WHEN 'California' THEN 'CA'
	WHEN 'Kentucky' THEN 'KY'
END abbrev
FROM #Temp_table

Notice in the simple CASE statement, the state field is used as our expression which means  our conditions are evaluated from the state field. These are referred to as “simple” because we’re not allowed to pass in logic that evaluates conditions from other fields. We’re confined to evaluating conditions that exist within the expression. 

Searched CASE statements in SQL

In my experience, the searched CASE statements in SQL are much more common than the simple CASE statements because they allow for complex condition logic. The searched CASE statement evaluates Boolean expressions to determine the result.

SELECT personID, [state],
CASE 
	WHEN [state] = 'Minnesota' THEN 'MN'
	WHEN [state] = 'California' THEN 'CA'
	WHEN [state] = 'Kentucky' THEN 'KY'
END abbrev
FROM #Temp_table

Since we’re not limited to evaluating conditions within an expression at the CASE level like we are in simple CASE expressions, we can pass in additional Boolean expressions in the WHEN conditions that search different fields:

SELECT personID, [state],
CASE
	WHEN [state] = 'Minnesota' AND Income >= 100000 THEN 'MN'
	WHEN [state] = 'California' THEN 'CA'
	WHEN [state] = 'Kentucky' THEN 'KY'
END abbrev
FROM #Temp_table

If we tried to pass AND income >= 100000 in the simple case expression, we’d get an error.

Filtering using CASE statements in SQL

It is possible to use CASE to filter data in the WHERE clause of a SQL query. This can be helpful if you want to restrict records based on logical conditions. 

SELECT personID, lastName, firstName, [state]
FROM #Temp_table
where(
CASE 
	WHEN [state] = 'Minnesota' AND Income >= 100000 THEN 'MN'
	WHEN [state] = 'California' THEN 'CA'
	WHEN [state] = 'Kentucky' THEN 'KY'
END) = 'MN' 

Notice we don’t alias the CASE statement when using it to filter since it doesn’t return a column in the query.

The CASE statement in SQL can also be used in the HAVING clause to filter data. Using the HAVING clause with CASE can sometimes feel unintuitive because you must put the CASE statement within an aggregate function. 

SELECT personID, lastName, firstName, [state]
FROM #Temp_table
GROUP BY personID, lastName, firstName, [state]
HAVING max(CASE 
		WHEN income < 100000 THEN 1 
                WHEN income BETWEEN 100000 AND 199999 THEN 2
                ELSE 3
                END) > 1

In the HAVING clause, we used max() as the aggregate function for this example, but we aren’t limited to that aggregate function. The query returns the records where the condition in the CASE statement resulted in a 2 or 3 because the HAVING clause filters out the records where the CASE statement’s condition returns a 1. 

Aggregating using CASE statements in SQL

One of the popular ways to use the CASE statement in SQL is for counting records that would be cumbersome to transpose using a count() function. For example, say we want to output a single row result set that returns the number of records from each state where the income is greater than or equal to 100,000:

select sum(CASE 
		WHEN [state] = 'Minnesota' and income >= 100000 THEN 1 
		ELSE 0 
		END) MN_counts,
	sum(CASE WHEN [state] = 'California' and income >= 100000 THEN 1
		ELSE 0
		END) CA_counts,
	sum(CASE WHEN [state] = 'Kentucky' and income >= 100000 THEN 1
		ELSE 0
		END) KY_counts
from #temp_table

By nesting the CASE statement within the sum() function, we can effectively count the number of records that meet our conditions by returning a 1 if the condition is true or a 0 if the condition is false. 

Nested CASE statements in SQL

One of the advanced use-cases for CASE statements in SQL is to nest CASE statements within another CASE statement. This technique is useful when you have sub-conditions that need to be evaluated.

SELECT personID, lastName, firstName, [state],
CASE 
	WHEN [state] = 'Minnesota' 
	THEN (CASE WHEN income > 100000 THEN 'over'
			ELSE 'not over' END)
	WHEN [state] = 'California' 
THEN (CASE WHEN income > 150000 THEN 'over'
			ELSE 'not over' END)
	WHEN [state] = 'Kentucky' 
THEN (CASE WHEN income > 75000 THEN 'over'
			ELSE 'not over' END)
END over_income
FROM #Temp_table

In the nested CASE statement example, the database first checks the state field. If the check returns true, it evaluates the sub conditions. For example, when the system evaluates records where the state is Minnesota, two records return as true. For those two true records, the income condition is evaluated and determines the output for the field. 

Multiple matches using CASE statements in SQL

If you’re getting unexpected output from your CASE statement, verify your logic doesn’t result in multiple matches. When more than one condition can be true, the database will always return the first true condition and ignore the rest.

select lastName, [state], income,
CASE 
	WHEN income > 50000 THEN 'greater than 50,000'
	WHEN income > 100000 THEN 'greater than 100,000'
	Else 'Income less than 50,000' 
end incomeCategory
from #temp_table

Notice how every incomeCategory output is greater than 50,000 even though there are income values greater than 100,000. Since the first condition in the CASE statement is true for every income value, the other conditions are never evaluated, thus we never get the output greater than 100,000.

Updating records using CASE statements in SQL

CASE statements in SQL can be placed in more than just SELECT statement queries. They can also be used when updating records. 

UPDATE #temp_table
set [state] = CASE 
		WHEN [state] = 'Minnesota' THEN 'MN'
		WHEN [state] = 'California' Then 'CA'
		WHEN [state] = 'Kentucky' Then 'KY'
	        END

When updating records, we set the value we want updated based on the conditions of the CASE statement. We could also update records based on filtering data using CASE statements:

update #temp_table
set income = null
WHERE (CASE 
		WHEN [state] = 'MN' and firstname = 'Oliver' THEN 1
		ELSE 0
		END ) = 1 

The CASE statement returns a 1 when the condition is true, and the WHERE clause filters out everything but rows that equal 1 resulting in only those rows being updated.

Deleting records using CASE statements in SQL

Similar to using CASE statements when updating records, we can also use CASE statements to determine which records get deleted by filtering. 

DELETE FROM #temp_table 
WHERE  (CASE 
		WHEN [state] = 'MN' and firstname = 'Oliver' THEN 1
		WHEN [state] = 'CA' and firstname = 'Kristen' Then 2
		ELSE 0
		END ) = 1 

Filtering using a CASE statement in the WHERE clause or HAVING clause can sometimes seem cumbersome, but can be very useful for when you need to check nested conditions before deleting or updating a row. 

4. Summary and next steps

In this article, we reviewed the CASE statement syntax and parameters, and then looked at several examples showing you exactly how to use CASE statements in SQL. 

Understanding CASE statements empowers you with versatile techniques for manipulating data. Whether you’re selecting, inserting, updating or deleting, you can use CASE statements to evaluate conditions and return results. CASE statements always contain the WHEN and THEN keywords. The first condition that is evaluated to be true returns a result based on what comes after the THEN keyword, and then the rest of the conditions are ignored.  

It takes practice to master databases and SQL, but it’s one of the best things you can do if you want a career in data analytics. Having been around for over 40 years, SQL is used worldwide and remains one of the most popular programming languages. 

If you want to make a career change in data analytics, try out this free 5-day short course.

To learn more about SQL and data analytics, check out some of our other articles:

• Common table expressions: When and how to use them
• Advanced SQL for data analytics
• What are CRUD operations?