How to Write a CASE Statement in SQL Database: PostgreSQL MS SQL Server MySQL Oracle SQLite Operators: CASE Table of Contents Problem: Example: Solution 1: Discussion: Solution 2: Discussion: Solution 3: Discussion: Problem: You want to use a CASE statement in SQL. Example: You have exam results in the exam table. You need to assign each result to one of the following text values: 'bad result', 'average result', or 'good result'. Bad results are those below 40, good results are those above 70, and the rest are average results. The exam table looks like this: nameresult Toby Shaw56 Casey Watson49 Bennie Lynn23 Lane Sloan70 Steff Fox85 Reggie Ward40 Gail Kennedy66 Brice Mueller90 Solution 1: SELECT name, result, CASE WHEN result < 40 THEN 'bad result' WHEN result > 70 THEN 'good result' ELSE 'average result' END AS category FROM exam; The result table looks like this: nameresultcategory Toby Shaw56average result Casey Watson49average result Bennie Lynn23bad result Lane Sloan70average result Steff Fox85good result Reggie Ward40average result Gail Kennedy66average result Brice Mueller90good result Discussion: To display a value based on your specific condition(s), you need to write a CASE statement. The syntax is: CASE WHEN <condition_1> THEN <value_1> WHEN <condition_2> THEN <value_2> … ELSE <value_n> END AS <column_name> If condition_1 is met, then the retrieved value is value_1. If not, then the database checks for condition_2. If condition_2 is true, then the retrieved value is value_2. If neither of these conditions is met, SQL checks for the remaining conditions one by one in order until one of the conditions is met. If none of the conditions is met, the value_n specified after the ELSE is retrieved. The ELSE part is optional. If you omit it and none of the conditions are met, you get a NULL. Remember to END the CASE clause when you're done with all of the conditions. Of course, as with any column you create, you can rename it (AS <column_name>). In our example, 'bad result' is assigned when result < 40, and 'good result' is assigned when result > 70. If neither of these conditions is met, the value is 'average result'. Also, to name the newly created column of the text values, you need to use an alias (AS category). This is how you get: CASE WHEN result < 40 THEN 'bad result' WHEN result > 70 THEN 'good result' ELSE 'average result' END AS category Since 'average result' is assigned to results between 40 and 70 (inclusive), you may also write a condition instead of ELSE (see Solution 2 below). However, it is easier to use ELSE. Solution 2: SELECT name, result, CASE WHEN result < 40 THEN 'bad result' WHEN result > 70 THEN 'good result' WHEN result >= 40 AND result <= 70 THEN 'average result' END AS category FROM exam; The result table looks like this: nameresultcategory Toby Shaw56average result Casey Watson49average result Bennie Lynn23bad result Lane Sloan70average result Steff Fox85good result Reggie Ward40average result Gail Kennedy66average result Brice Mueller90good result Discussion: Since 'average result' is assigned to results between 40 and 70 (inclusive), you may write the following condition instead of an ELSE: WHEN result >= 40 AND result <= 70 THEN 'average result' If you do not use an ELSE and do not want any NULLs in the category column, you need to make sure you take care of all possible result values. If there's a result that meets none of the conditions, you get a NULL. Solution 3: SELECT name, result, CASE WHEN result < 40 THEN 'bad result' WHEN result <= 70 THEN 'average result' ELSE 'good result' END AS category FROM exam; The result table looks like this: nameresultcategory Toby Shaw56average result Casey Watson49average result Bennie Lynn23bad result Lane Sloan70average result Steff Fox85good result Reggie Ward40average result Gail Kennedy66average result Brice Mueller90good result Discussion: The specified conditions in CASE can be non-overlapping as in the previous solutions or overlapping as in this solution. The first condition is the same as before – for result < 40, the category is 'bad result'. All results below 40 are assigned this value at this point and you don't need to do anything with them anymore. This means there is no need to make sure result >= 40 in specifying the conditions for the 'average result' – all results less than 40 already have the value 'bad result' assigned. Finally, for the remaining results the category should be 'good result'; a simple ELSE 'good result' takes care of it. This is how you get: CASE WHEN result < 40 THEN 'bad result' WHEN result <= 70 THEN 'average result' ELSE 'good result' END AS category Note: The order of the conditions in CASE matters. If you reverse the order of the first two conditions (i.e., if you specify WHEN result <= 70 THEN 'average result' first then WHEN result <= 70 THEN 'average result' second), all results less than or equal to 70 end up in the 'average result' category, with no results assigned to the 'bad result' category. Recommended courses: SQL Basics SQL Practice Set Creating Basic SQL Reports Recommended articles: SQL for Data Analysis Cheat Sheet Using CASE with Data Modifying Statements How to Use CASE WHEN With SUM() in SQL See also: How to Filter Records with Aggregate Function COUNT How to Group by Two Columns in SQL Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query