Using CASE to Add Logic to a SELECT

As you write an SQL query, you may need to get values from multiple columns and change values from one form to another. The simple way to achieve this goal is to add a CASE expression to your SELECT statement. In this article, we'll introduce you to the syntax, formats, and uses of the CASE expression.

The CASE expression is a conditional expression: it evaluates data and returns a result. The result it returns is based on whether the data meets certain criteria. This expression can be used in SELECT, UPDATE, and DELETE statements or in WHERE, IN, LIKE, ORDER BY, and HAVING clauses. We'll focus on the SELECT statement and use IN and LIKE operators.

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.

You should know that there are two kinds of CASE expressions: simple and searched. We'll look at both, but first we will start with the basic syntax of this expression.

CASE Syntax

The basic syntax of the CASE expression is presented below:

CASE [ column or expression ]
  WHEN value or condition THEN when_result
  ...
  ELSE else_result
END

The expression starts with the CASE keyword and ends with the END keyword. The names of specific columns or expressions are entered after the CASE keyword. The WHEN and THEN keywords define the logical conditions. After the WHEN clause, we see the condition or value to compare; the THEN determines the result if the condition is met. An optional ELSE statement determines the result returned if the WHEN condition is not met. If ELSE is not defined and WHEN is not met, a NULL will be returned.

There are two types of CASE expressions: simple and searched. Each one has a slightly different syntax, and each works a little differently.

We'll begin with the simple format.

CASE: The Simple Format

The simple CASE compares a value to one or more WHEN conditions. If it meets a WHEN condition, the THEN result is returned. Here's what the syntax looks like:

CASE column_or_expression
  WHEN value THEN when_result
  ...
  ELSE else_result
END

In a simple CASE expression, the name of the column or expression to be evaluated is absolutely necessary. It goes after the CASE keyword. The set value goes after the WHEN. If the evaluated value is the same the as the set value, the result defined in THEN is returned. If the value from the column or expression doesn't match any WHEN values, then the result of ELSE is displayed. Remember, ELSE is optional.

The following example queries are based on data from the animal table, as shown below:

Look at this simple CASE:

CASE zoo 
  WHEN 1 THEN 'Brookfield Zoo'
  WHEN 2 THEN 'Dublin zoo'
  ELSE 'unknown'
END 

This example selects data from the animal table and displays the name of the animal's current zoo. If zoo=1, then Brookfield Zoo will be displayed; if zoo=2 then we'll see Dublin Zoo; all other cases will display as unknown.

The result is:

Notice that if the value from the zoo column is not the same as either of the values in the WHEN clauses (or is NULL), the result is unknown.

Using Aliases in CASE Statements

The CASE expression can have an alias, or a name for the resulting new column. The above query, now with an alias, looks like this:

SELECT name, zoo, 
  CASE zoo 
    WHEN 1 THEN 'Brookfield Zoo'
    WHEN 2 THEN 'Dublin zoo'
    ELSE 'unknown'
  END AS name_zoo
FROM animal;

And the result:

Using CASE with Other Data Types

You can use the CASE expression with other data types, such as text data types.

Check out this query:

SELECT name, zoo, 
  CASE country 
    WHEN 'Russia' THEN 'Europe'
    WHEN 'Congo' THEN 'Africa'
    WHEN 'Turkey' THEN 'Asia'
    WHEN 'Australia' THEN 'Australia' 
    WHEN 'China' THEN 'Asia'
    ELSE 'unknown'
  END AS continent
FROM animal;

This query selects data from the table and displays the continent where the animal comes from. The continent is based on the country column, which uses a text data type.

This is the result:

The Searched CASE Expression

The searched CASE expression is the most commonly-used format. Instead of comparing a column or expression against a defined set of values, a searched expression can compare multiple WHEN conditions and determine a result. This makes a searched CASE the better choice for more complicated logic.

Here's the syntax:

CASE 
    WHEN conditions THEN when_result
    ...
    ELSE else_result
END

Once again, the expression begins with the CASE keyword, but there is no column name or expression following. There can be multiple WHEN – THEN constructions, just like the simple CASE. But after the WHEN keyword, we enter one or more conditions rather than set values. These conditions will compare a retrieved value and return the first one it meets as TRUE. This will cause the result from THEN to display. If all conditions return FALSE, then the result from ELSE is displayed. And again, ELSE is optional.

This might seem hard to understand, so let's dig into our example query:

SELECT  name, weight,
  CASE 
    WHEN weight > 1000 THEN 'big'
    WHEN weight BETWEEN 100 AND 1000 THEN 'middle'
    WHEN weight < 100 THEN 'small'
    ELSE '---'
  END AS description_weight
FROM animal ;

Look at the results:

The first WHEN clause checks if weight is greater than 1,000 kilograms. If it is true, the big string is returned. The next WHEN condition checks if weight falls between 100 and 1,000 kilograms. If it does, the middle string will be returned. In this case, the BETWEEN AND operator is used to define the range. For animals whose weight is less than 100 kilograms, the small string will be displayed. In any remaining cases, the return value will be ---.

Ordering Conditions in CASE

So now you can build conditions in the WHEN clause. But it's important to build them accurately. If your test value matches the first WHEN clause, the THEN clause is returned and the CASE expression is finished. Even if a value actually meets several WHEN conditions, the first condition it meets is the only one that will be returned.

Look at the following example:

SELECT name, weight,
  CASE 
    WHEN weight<1000 THEN 'not big'
    WHEN weight<100 THEN 'not middle'
    WHEN weight<10 THEN 'very small'
    ELSE '---'
  END AS description_weight
FROM animal ;

In this case, the weight value will be the first to fit the initial WHEN condition. In our table, most of the animals will have a weight value of less than 1,000 kg. Only animals with an associated weight of 1,000 or more (or who have a NULL value in weight) will get a --- value returned.

Here are the results:

Remember that the order of the WHEN conditions is very important; they can affect the results that you get back.

Learning More About the SQL CASE Expression

Being able to use a properly-formatted CASE expression is a good ability to nurture. It allows you to get the results you expect from your queries.

If you are interested in learning more about the CASE expression, check out our interactive Standard SQL Functions course. It will show you how to build queries that use this expression and many others. You'll also get a chance to practice your skills. Try it today!