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.

Up til now, we've considered simple CASE expressions that have a column name after the CASE keyword. You can also put an expression after the CASE keyword.

Imagine that, besides animals' weights in kilograms, you would also like to display the total weight of mammals by species – but only if these values are special. In this case, three characteristic numbers must be determined. They will be displayed as strings. If the total weight is not one of these three numbers, then other will be shown.

Here is the query:

SELECT name, weight, number_of_animal,
		( CASE  number_of_animal*weight 
  			WHEN 18 THEN 'small'
  			WHEN 360 THEN 'middle'
  			WHEN 24000 THEN 'big'
  			ELSE 'other'
		  END 
                 ) as total_weight
FROM  animal ;

Look at the result:

This CASE expression has an ELSE clause that displays other. Without the ELSE, all non-matching weights would display a NULL. Here's the same code without an ELSE:

SELECT  name, weight, number_of_animal,
			 CASE  number_of_animal*weight 
  				WHEN 18 THEN 'small'
  				WHEN 360 THEN 'middle'
  				WHEN 24000 THEN 'big'
			 END 
                         as total_weight
FROM  animal ;

And the results:

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 CHARACTER.

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 the CHARACTER data type.

This is the result:

The Searched CASE Expression

The searched CASE expression is the most commonly-used format. But 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 "— ".

Using CASE with LIKE

You can use other operators with the CASE expression. In the following example, we will use the LIKE operator, which matches patterns in search substrings. (For more information on the LIKE operator, read Using LIKE to Match Patterns in SQL.)

Here is the example:

SELECT name, 
		( CASE 
  			WHEN name LIKE '____' THEN '4 letters'
  			WHEN name LIKE '_____' THEN '5 letters'
 			ELSE 'other'
		  END 
		) as description
FROM  animal ;

And here is the result:

In this searched CASE expression, the LIKE operator tests if the animal's name consists of four or five letters. In other cases – as in the African elephant – the return value is other.

Using CASE with IN

In the next example, we'll try out the IN operator. Remember the simple CASE expression that returned a continent name based on the country associated with an animal? The code for this query was long, thanks to lots of separate WHEN – THEN constructions. Below, we give you another look at the old code:

SELECT  name, country,
			( 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 ;

The IN operator allows you to reduce the amount of code. Look at a searched CASE with the IN operator:

SELECT name, country, 
		( CASE   
 		    WHEN country IN ('Russia') THEN 'Europe'
 		    WHEN country IN ('Congo') THEN 'Africa'
		    WHEN country IN ('Turkey','China') THEN 'Asia'
 		    WHEN country IN ('Australia') THEN 'Australia' 
	            ELSE 'unknown'
		  END 
		) as continent
FROM  animal ;

We can use the IN operator to list multiple countries in the same continent. If the value from the country column matches one of the strings in the IN, the result is the continent from the THEN clause. Of course, if the country name is not listed in any IN operators, ELSE kicks in and returns an unknown value.

Look at the results table below:

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 LearnSQL's 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!