FB

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

In this article, you’ll learn:

  • What the CASE statement is
  • The syntax and parameters of the CASE statement
  • What the difference between a simple and searched CASE statement is
  • The answers to a few FAQ
  • 9 examples of CASE statements with their results

 

What Does the Oracle 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. This logic used to only be possible using a DECODE function. However, the CASE SQL statement was introduced in Oracle 9i.

 

CASE Statement Syntax

The syntax of the Oracle CASE expression is:

CASE [expression]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2 ...
WHEN condition_n THEN result_n
ELSE result
END 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 expression 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 Oracle 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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

 

Simple and Searched CASE Expressions

There are actually two ways to use an Oracle CASE statement, which Oracle refer 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:

CASE name
  WHEN 'John' THEN 'Name is John'
  WHEN 'Steve' THEN 'Name is Steve'
END

 

Searched CASE expression

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

For example:

CASE
  WHEN name = 'John' THEN 'Name is John'
  WHEN name = 'Steve' THEN 'Name is Steve'
END

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 Oracle 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 Oracle 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 Oracle SQL IF statement 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 Oracle 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

SELECT
first_name, last_name, country,
CASE country
  WHEN 'USA' THEN 'North America'
  WHEN 'Canada' THEN 'North America'
  WHEN 'UK' THEN 'Europe'
  WHEN 'France' THEN 'Europe'
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;

 

FIRST_NAME LAST_NAME COUNTRY CONTINENT
Adam Cooper USA North America
John Smith USA North America
Mark Allan UK Europe
Sally Jones USA North America
Steve Brown Canada North America

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.

Notice the name of the column. It’s a bit messy.

 

Simple CASE Statement with Named Column

SELECT
first_name, last_name, country,
CASE country
  WHEN 'USA' THEN 'North America'
  WHEN 'Canada' THEN 'North America'
  WHEN 'UK' THEN 'Europe'
  WHEN 'France' THEN 'Europe'
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;
FIRST_NAME LAST_NAME COUNTRY CONTINENT
Adam Cooper USA North America
John Smith USA North America
Mark Allan UK Europe
Sally Jones USA North America
Steve Brown Canada North America

This is the same example as above, but we’ve named the column “Continent”. Notice how it looks neater.

 

Searched Case

SELECT first_name, last_name, country,
CASE
  WHEN country = 'USA' THEN 'North America'
  WHEN country = 'Canada' THEN 'North America'
  WHEN country = 'UK' THEN 'Europe'
  WHEN country = 'France' THEN 'Europe'
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;
FIRST_NAME LAST_NAME COUNTRY CONTINENT
Adam Cooper USA North America
John Smith USA North America
Mark Allan UK Europe
Sally Jones USA North America
Steve Brown Canada North America

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

SELECT first_name, last_name, employees,
CASE
  WHEN employees < 10 THEN 'Small'
  WHEN employees >= 10 AND employees <= 50 THEN 'Medium'
  WHEN employees >= 50 THEN 'Large'
END SizeOfCompany
FROM customers
ORDER BY first_name, last_name;
FIRST_NAME LAST_NAME EMPLOYEES SIZEOFCOMPANY
Adam Cooper 55 Large
John Smith 4 Small
Mark Allan 23 Medium
Sally Jones 10 Medium
Steve Brown 15 Medium

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

SELECT first_name, last_name, country,
CASE
  WHEN country IN ('USA', 'Canada') THEN 'North America'
  WHEN country IN ('UK', 'France') THEN 'Europe'
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;
FIRST_NAME LAST_NAME COUNTRY CONTINENT
Adam Cooper USA North America
John Smith USA North America
Mark Allan UK Europe
Sally Jones USA North America
Steve Brown Canada North America

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.

 

CASE Within CASE Statement

SELECT first_name, last_name, country,
CASE
  WHEN country IN ('USA', 'Canada') THEN
    (CASE WHEN first_name = 'Sally' THEN 'North America F' ELSE 'North America M' END)
  WHEN country IN ('UK', 'France') THEN
    (CASE WHEN first_name = 'Sally' THEN 'Europe F' ELSE 'Europe M' END)
  ELSE 'Unknown'
END Continent
FROM customers
ORDER BY first_name, last_name;
FIRST_NAME LAST_NAME COUNTRY CONTINENT
Adam Cooper USA North America M
John Smith USA North America M
Mark Allan UK Europe M
Sally Jones USA North America F
Steve Brown Canada North America M

This example shows a CASE statement within another CASE statement. 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.

 

CASE Statement with Functions

SELECT first_name, last_name, employees,
CASE
  WHEN MOD(employees, 2) = 0 THEN 'Even Number of Employees'
  WHEN MOD(employees, 2) = 1 THEN 'Odd Number of Employees'
  ELSE 'Unknown'
END OddOrEven
FROM customers
ORDER BY first_name, last_name;
FIRST_NAME LAST_NAME EMPLOYEES ODDOREVEN
Adam Cooper 55 Odd Number of Employees
John Smith 4 Even Number of Employees
Mark Allan 23 Odd Number of Employees
Sally Jones 10 Even Number of Employees
Steve Brown 15 Odd Number of Employees

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

SELECT first_name, last_name, employees,
CASE
  WHEN employees < 1 THEN 'No Employees'
  WHEN employees < 10 THEN 'Small'
  WHEN employees <= 50 THEN 'Medium'
  WHEN employees >= 50 THEN 'Large'
END SizeOfCompany
FROM customers
ORDER BY first_name, last_name;
FIRST_NAME LAST_NAME EMPLOYEES SIZEOFCOMPANY
Adam Cooper 55 Large
John Smith 4 Small
Mark Allan 23 Medium
Sally Jones 10 Medium
Steve Brown 15 Medium

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 in a WHERE Clause

SELECT first_name, last_name, country
FROM customers
WHERE
  (CASE
    WHEN country IN ('USA', 'Canada') THEN 'North America'
    WHEN country IN ('UK', 'France') THEN 'Europe'
    ELSE 'Unknown'
  END) = 'North America'
ORDER BY first_name, last_name;
FIRST_NAME LAST_NAME COUNTRY
Adam Cooper USA
John Smith USA
Sally Jones USA
Steve Brown Canada

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 Oracle 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 Oracle CASE statement. If there is no result, and there is no ELSE statement, then the value of NULL is returned.

 

Does Oracle Search All Conditions Or Just Finds The First Match?

A common question on Oracle CASE statements is if the Oracle 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. Oracle 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 Oracle CASE Statement In WHERE Clause?

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

 

Can You Use An Oracle CASE within CASE?

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

 

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.

 

Oracle PL/SQL Does Have an IF Statement

Oracle’s procedural language, PL/SQL, does have an IF statement.

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.

 

What About DECODE?

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

It looks like this:

DECODE (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.

 

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

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Image courtesy of FreeDigitalPhotos.net

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Get Your FREE PDF: 9 Ways to Improve your Database Skills