7th May 2017 10 minutes read Using CASE to Add Logic to a SELECT Dorota Wdzięczna CASE WHEN SELECT Table of Contents CASE Syntax CASE: The Simple Format Using Aliases in CASE Statements Using CASE with Other Data Types The Searched CASE Expression Using CASE with LIKE Using CASE with IN Ordering Conditions in CASE Learning More About the SQL CASE Expression 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: id name weight country number_of_animal zoo 1 Tiger 120 Russia 3 1 2 African elephant 4 800 Congo 5 1 3 Lion 175 Turkey 1 1 4 Koala 9 Australia 2 NULL 5 Giraffe 800 5 2 6 Gaur NULL China 2 1 7 Okapi 225 NULL 5 0 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: name zoo case Tiger 1 Brookfield Zoo African elephant 1 Brookfield Zoo Lion 1 Brookfield Zoo Koala NULL unknown Giraffe 2 Dublin zoo Gaur 1 Brookfield Zoo Okapi 0 unknown 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: name weight number_of_animal total_weight Tiger 120 3 middle African elephant 4 800 5 big Lion 175 1 other Koala 9 2 small Giraffe 800 5 other Gaur NULL 2 other Okapi 225 5 other 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: name weight number_of_animal total_weight Tiger 120 3 middle African elephant 4 800 5 big Lion 175 1 NULL Koala 9 2 small Giraffe 800 5 NULL Gaur NULL 2 NULL Okapi 225 5 NULL 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: name zoo name_zoo Tiger 1 Brookfield Zoo African elephant 1 Brookfield Zoo Lion 1 Brookfield Zoo Koala NULL unknown Giraffe 2 Dublin zoo Gaur 1 Brookfield Zoo Okapi 0 unknown 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: name zoo continent Tiger 1 Europe African elephant 1 Africa Lion 1 Asia Koala NULL Australia Giraffe 2 unknown Gaur 1 Asia Okapi 0 unknown 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: name weight description_weight Tiger 120 middle African elephant 4 800 big Lion 175 middle Koala 9 small Giraffe 800 middle Gaur NULL — Okapi 225 middle 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: name description Tiger 5 letters African elephant other Lion 4 letters Koala 5 letters Giraffe other Gaur 4 letters Okapi 5 letters 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: name country continent Tiger Russia Europe African elephant Congo Africa Lion Turkey Asia Koala Australia Australia Giraffe unknown Gaur China Asia Okapi NULL unknown 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: name weight description_weight Tiger 120 not big African elephant 4 800 — Lion 175 not big Koala 9 not big Giraffe 800 not big Gaur NULL — Okapi 225 not big 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! Tags: CASE WHEN SELECT