SQL CASE Statement Explained with Examples

The SQL CASE statement allows you to perform IF-THEN-ELSE functionality within an SQL statement. Learn more about this powerful statement in this article.

What Does the SQL CASE Statement Do?

The CASE statement allows you to perform an IF-THEN-ELSE check within an SQL statement.

It's good for displaying a value in the SELECT query based on logic that you have defined. As the data for columns can vary from row to row, using a CASE SQL expression can help make your data more readable and useful to the user or to the application.

It's quite common if you're writing complicated queries or doing any kind of ETL work.

SQL CASE Statement Syntax

The syntax of the SQL CASE expression is:

1CASE [expression]
2WHEN condition_1 THEN result_1
3WHEN condition_2 THEN result_2 ...
4WHEN condition_n THEN result_n
5ELSE result
6END case_name

The CASE statement can be written in a few ways, so let's take a look at these parameters.

Parameters of the CASE Statement

The parameters or components of the CASE SQL statement are:

• expression (optional): This is the expression that the CASE statement looks for. If we're comparing this to an IF statement, this is the check done inside the IF statement (e.g. for IF x > 10, the expression would be "x > 10"
• condtion_1/condition_n (mandatory): These values are a result of the expression parameter mentioned. They are the possible values that expression can evaluate to. Alternatively, they can be an expression on their own, as there are two ways to write an SQL CASE statement (as explained below). They also relate to the IF statement in the IF-THEN-ELSE structure.
• result_1/result_n (mandatory): These values are the value to display if the related condition is matched. They come after the THEN keyword and relate to the THEN part of the IF-THEN-ELSE structure.
• result (optional): This is the value to display if none of the conditions in the CASE statement are true. It is the ELSE part of the IF-THEN-ELSE structure and is not required for the CASE SQL statement to work.
• case_name (optional): This value indicates what the column should be referred to as when displayed on the screen or from within a subquery. It's also called the column alias.

While we're here, if you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here:

Simple and Searched CASE Expressions

There are actually two ways to use an SQL CASE statement, which are referred to as a "simple case expression" or a "searched case expression".

Simple CASE expression

The expression is stated at the beginning, and the possible results are checked in the condition parameters.

For example:

1CASE name
2  WHEN 'John' THEN 'Name is John'
3  WHEN 'Steve' THEN 'Name is Steve'
4END

Searched CASE expression

The expressions are used within each condition without mentioning it at the start of the CASE statement.

For example:

1CASE
2  WHEN name = 'John' THEN 'Name is John'
3  WHEN name = 'Steve' THEN 'Name is Steve'
4END

All data types for the expression and conditions for the Simple expressions, and all of the results for both expression types must be the same or have a numeric data type. If they all are numeric, then the database will determine which argument has the highest numeric precedence, implicitly convert the remaining argument to that data type, and return that datatype. Basically, it means the database will work out which data type to return for this statement if there is a variety of numeric data types (NUMBER, BINARY_FLOAT or BINARY_DOUBLE for example).

In SQL, IF statements in SELECT statements can be done with either of these methods. I'll demonstrate this using more examples later in this article.

Want to see guides like this for all other Oracle functions? Check out this page here that lists all SQL functions along with links to their guides.

Examples of the CASE Statement

Here are some examples of the SQL CASE statement in SELECT queries. I find that examples are the best way for me to learn about code, even with the explanation above.

Simple CASE Statement

 1SELECT
 2first_name, last_name, country,
 3CASE country
 4  WHEN 'USA' THEN 'North America'
 5  WHEN 'Canada' THEN 'North America'
 6  WHEN 'UK' THEN 'Europe'
 7  WHEN 'France' THEN 'Europe'
 8  ELSE 'Unknown'
 9END Continent
10FROM customers
11ORDER BY first_name, last_name;

The results are:

This example is using the simple case statement structure. Notice how the expression (in this case the "country" field) comes right after the CASE keyword. This means the WHEN expressions are all compared to that field.

This example, like most of the examples here, shows the continent of each customer, based on their country.

Searched Case

 1SELECT first_name, last_name, country,
 2CASE
 3  WHEN country = 'USA' THEN 'North America'
 4  WHEN country = 'Canada' THEN 'North America'
 5  WHEN country = 'UK' THEN 'Europe'
 6  WHEN country = 'France' THEN 'Europe'
 7  ELSE 'Unknown'
 8END Continent
 9FROM customers
10ORDER BY first_name, last_name;

This example performs the same check as the other examples but uses the searched case method. This means that each expression in the WHEN section is evaluated individually. It gives the same result as the previous example though.

Searched Case with Numbers

1SELECT first_name, last_name, employees,
2CASE
3  WHEN employees < 10 THEN 'Small'
4  WHEN employees >= 10 AND employees <= 50 THEN 'Medium'
5  WHEN employees >= 50 THEN 'Large'
6END SizeOfCompany
7FROM customers
8ORDER BY first_name, last_name;

This example performs a searched CASE using a number field, which is the number of employees. Notice how the second WHEN expression has two checks - to see if the number is between 10 and 50.

CASE Statement With IN Clause

1SELECT first_name, last_name, country,
2CASE
3  WHEN country IN ('USA', 'Canada') THEN 'North America'
4  WHEN country IN ('UK', 'France') THEN 'Europe'
5  ELSE 'Unknown'
6END Continent
7FROM customers
8ORDER BY first_name, last_name;

This example looks up the continent of the customer again. However, it uses an IN clause, which means the value is checked to see if it is in the IN parameter. It should have the same result, but it's a bit cleaner and has less code.

Nested CASE Statement in SQL

This example shows a CASE statement within another CASE statement, also known as a "nested case statement" in SQL.

It first checks the country and then checks for a particular customer name to see if it is male or female (given that Sally is the only female here).

Notice how I didn't give a name to the inner case statement. I didn't need to - this is not displayed and the name is already specified for the Continent column.

 1SELECT first_name, last_name, country,
 2CASE
 3  WHEN country IN ('USA', 'Canada') THEN
 4    (CASE WHEN first_name = 'Sally' THEN 'North America F' ELSE 'North America M' END)
 5  WHEN country IN ('UK', 'France') THEN
 6    (CASE WHEN first_name = 'Sally' THEN 'Europe F' ELSE 'Europe M' END)
 7  ELSE 'Unknown'
 8END Continent
 9FROM customers
10ORDER BY first_name, last_name;

We can see that the results show different values based on the nested CASE statement.

CASE Statement with Functions

1SELECT first_name, last_name, employees,
2CASE
3  WHEN MOD(employees, 2) = 0 THEN 'Even Number of Employees'
4  WHEN MOD(employees, 2) = 1 THEN 'Odd Number of Employees'
5  ELSE 'Unknown'
6END OddOrEven
7FROM customers
8ORDER BY first_name, last_name;

This example uses the MOD function to demonstrate how you can use CASE statements with functions. It checks the number of employees and determines if they have an odd or even number of employees.

Multiple Matches

1SELECT first_name, last_name, employees,
2CASE
3  WHEN employees < 1 THEN 'No Employees'
4  WHEN employees < 10 THEN 'Small'
5  WHEN employees <= 50 THEN 'Medium'
6  WHEN employees >= 50 THEN 'Large'
7END SizeOfCompany
8FROM customers
9ORDER BY first_name, last_name;

This example shows what happens if there are records that match with multiple WHEN expressions. For example, some customers may have both <1 employees and <10 employees. What happens here? The CASE statement finds the first matching expression and uses that.

CASE Statement in WHERE Clause

1SELECT first_name, last_name, country
2FROM customers
3WHERE
4  (CASE
5    WHEN country IN ('USA', 'Canada') THEN 'North America'
6    WHEN country IN ('UK', 'France') THEN 'Europe'
7    ELSE 'Unknown'
8  END) = 'North America'
9ORDER BY first_name, last_name;

This example shows how the CASE statement is used in a WHERE clause.

This example shows all customers who live in North America, using the CASE statement to restrict the records.

CASE Statement Frequently Asked Questions

What's an IF Statement?

In case you're not sure, an IF statement allows you to do something if a condition is true, and something else if the condition is false.

It's a common feature of many programming languages.

However, SQL isn't like other programming languages.

It's not procedural. It doesn't make several steps on different variables to get the result you want.

It's set based. You tell the database everything you want, and it returns a set of results.

So, how can you have an SQL IF statement?

I'll be writing about how to write the IF statement in SQL.

What If No Match Is Found In A CASE Statement?

If there is no match found in any of the conditions, that's where the ELSE statement comes in. The value used in the ELSE statement is what is returned if no match is found.

However, this is an optional part of the SQL CASE statement. If there is no result, and there is no ELSE statement, then the value of NULL is returned.

Does The CASE Statement Search All Conditions Or Just Finds The First Match?

A common question on SQL CASE statements is if the database evaluates all of the conditions in the CASE statement, or does it stop after finding the first match?

The answer is that it stops after the first match. It finds the first match, or the first expression that is evaluated to be a match, and does not continue with the rest.

It also performs something called "short-circuit evaluation" for Simple CASE expressions. This might not be a concern to you, but it's good to know for performance reasons. The database will evaluate the first condition, then compare it to the expression, then evaluate the second condition, then evaluate that to the expression, and so on. It doesn't evaluate all conditions before comparing the first one to the expression.

How Many Conditions or Arguments Can a CASE Statement Use?

The maximum number of conditions in a CASE statement is 255. This includes:

• The initial expression in a simple CASE statement
• The optional ELSE expression
• The expression for the WHEN condition
• The expression for the THEN result

You can use nested CASE statements so that the return value is a CASE expression. However, if you're reaching the limit of 255 expressions, I would be looking at the efficiency of the query itself, as most queries should not need 255 expressions.

Can You Use An SQL CASE Statement In WHERE Clause?

Yes, you can use an SQL CASE in a WHERE clause. The examples below will show how this is done.

Can You Use An SQL CASE within CASE?

Yes, you can use a CASE within CASE in SQL. The examples below will show how this is done.

Can I Use DECODE Instead Of CASE?

Oracle has a function called DECODE, which lets you check an expression and return different values.

It looks like this:

1DECODE (expression, condition1, result1, condition_n, result_n)

However, CASE is recommended for several reasons:

• DECODE is older, and CASE was made as a replacement for DECODE.
• CASE offers more flexibility.
• CASE is easier to read.

In terms of performance, they are both very similar. Experiments have shown that unless you're using millions of records, you won't get much of a difference, and any difference will be small.

MySQL has a DECODE function but it's used for something completely different. SQL Server and PostgreSQL don't have a DECODE function.

There Is No IIF or IF in Oracle

SQL Server 2012 introduced a statement called IIF, which allows for an IF statement to be written.

However, Oracle does not have this functionality. It's SQL Server only.

The CASE statement should let you do whatever you need with your conditions.

In Oracle, there is no "IF" statement or keyword specifically in Oracle. If you want to use IF logic, then use the CASE statement.

Procedural Languages Have an IF Statement

The procedural languages for each database do have an IF statement:

• Oracle PL/SQL
• SQL Server T-SQL
• MySQL
• PostgreSQL PGSQL

This statement works just like other languages. You have IF, ELSE, ELSIF and END.

However, that's when you're working with PL/SQL.

If you're writing functions or stored procedures, you could use this IF statement.

If you're just using standard SQL in your application or database, then you can use the CASE statement.

Conclusion

Hopefully, that explains how the SQL CASE statement is used and answers any questions you had. If you want to know more, just leave a comment below.

If you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here: