MySQL CASE Expression

INTRODUCTION

 
In this tutorial, I am going to explain CASE expressions in MySQL with examples. This article will cover the following topics. Let’s see.
  1. Introduction
  2. Simple CASE Expression
  3. Searched CASE Expression
  4. Difference between SIMPLE and SEARCHED CASE Expressions
  5. Conclusion
In MySQL, the case expression shows multiple conditions. An expression is the compatible aggregated type of all return values, but also depends on the context. If we have to use it in a numeric context, the result is returned as a decimal, real, or integer value. You can use CASE expression anywhere in a query like SELECT, WHERE, or ORDER BY clause.
 
Note:
Note that, MySQL CASE Expressions are different from MySQL CASE Statements. MySQL CASE Statements are only used in Stored Procedures, Functions, Events, and Triggers whereas MySQL CASE Expressions are used in SELECT Queries.
 
The CASE expression has two forms
  1. Simple CASE Statement
  2. Searched CASE Statement
First, let's create a database with a few tables containing some dummy data. Here, I am providing the database with the tables containing the records, on which I am showing you the various examples. Let's see.
  1. CREATE DATABASE MySQL_CASEExpressions;  
  2.   
  3. USE MySQL_CASEExpressions;  
  4.   
  5. CREATE TABLE Bookorder(  
  6.   BookNumber INT NOT NULL,  
  7.   orderDate datetime NOT NULL,  
  8.   shippedDate DATE DEFAULT NULL,  
  9.   Status VARCHAR(50),  
  10.   OrderAcknowledgeDate datetime,  
  11.   PRIMARY KEY(BookNumber)  
  12. ); 

SIMPLE CASE Expressions

 
In the Simple Case Expressions, when “case_value” matches with its respective “value”, then it returns the corresponding “result” value. But, if the “case_value” does not match with any “value” then, it returns the “result_value”.
 
Syntax
 
CASE <case_value>
WHEN <value1> THEN <result1>
WHEN <value2> THEN <result2>...
ELSE <result_value> END;
 
Example 1
  1. SELECT CASE 10 * 2 WHEN 25 THEN 'Incorrect'  
  2. WHEN 40 THEN '40 Incorrect'  
  3. ELSE 20 END AS CASE_Result; 
 
Example 2
  1. SELECT CASE 5 * 5 - 5 / 5 + 6 WHEN 30 THEN "TRUE"  
  2. ELSE "FALSE"  
  3. END AS "Results"
 
Example 3
  1. SELECT CASE 10 * 2  
  2. WHEN 20 THEN '20 correct'  
  3. WHEN 30 THEN '30 correct'  
  4. WHEN 40 THEN '40 correct'  
  5. END AS Result; 
 
Example 4
  1. SELECT BookNumber, orderDate,  
  2. CASE Status  
  3. WHEN "Done"  
  4. THEN "Product_Shipped"  
  5. WHEN "In Progress"  
  6. THEN "Product_Not_Shipped"  
  7. END AS ProductStatus  
  8. FROM mysql_caseexpressions.bookorder; 
 
Example 5 – With Aggregate Function
  1. SELECT SUM(CASE Status WHEN 'Done'  
  2.     THEN 1 ELSE 0 ENDAS 'Product_Shipped',  
  3.   SUM(CASE Status WHEN 'In Progress'  
  4.     THEN 1 ELSE 0 ENDAS 'Product_Not_Shipped',  
  5.   COUNT( * ) AS TotalOrders  
  6. FROM Bookorder; 
 

SEARCHED CASE Expressions

 
In the Searched Case Expressions, CASE evaluates the “expressions” values that are specified in the WHEN clause and returns the corresponding “result_set” value. But, if any value is not satisfied by the corresponding conditions then, it returns the “result_value” that is specified in the ELSE clause.
 
Syntax
 
CASE
WHEN <expression1> THEN <result_set1>
WHEN <expression2> THEN <result_set2> …
ELSE <result_value>
END
 
Example 1
  1. SELECT CASE WHEN 10 * 2 = 25 THEN 'Incorrect'  
  2. WHEN 10 * 2 = 40 THEN '40 Incorrect'  
  3. ELSE "Should be 10*2=20"  
  4. END AS SEARCHED_CASE_Result; 
 
Example 2
  1. SELECT CASE WHEN 5 * 5 - 5 / 5 + 6 = 30 THEN "TRUE"  
  2. ELSE "FALSE"  
  3. END AS SEARCHED_CASE_Result; 
 
Example 3
  1. SELECT CASE WHEN 10 * 2 = 20 THEN '20 correct'  
  2. WHEN 10 * 2 = 30 THEN '30 correct'  
  3. WHEN 10 * 2 = 40 THEN '40 correct'  
  4. END AS SEARCHED_CASE_Result; 
 
Example 4
  1. SELECT BookNumber, orderDate,  
  2. CASE WHEN Status = "Done"  
  3. THEN "Product_Shipped"  
  4. WHEN Status = "In Progress"  
  5. THEN "Product_Not_Shipped"  
  6. END AS ProductStatus  
  7. FROM mysql_caseexpressions.bookorder; 
 
Example 5 – With Aggregate Function
  1. SELECT SUM(CASE WHEN Status = 'Done'  
  2.     THEN 1 ELSE 0 ENDAS 'Product_Shipped',  
  3.   SUM(CASE WHEN Status = 'In Progress'  
  4.     THEN 1 ELSE 0 ENDAS 'Product_Not_Shipped',  
  5.   COUNT( * ) AS TotalOrders  
  6. FROM Bookorder; 
 

Difference between SIMPLE and SEARCHED CASE Expressions

 
Example 1
  1. SELECT CASE WHEN 10 * 2 = 20 THEN '20 correct'  
  2. WHEN 10 * 2 = 30 THEN '30 correct'  
  3. WHEN 10 * 2 = 40 THEN '40 correct'  
  4. END AS SEARCHED_CASE_Result,  
  5.   
  6. CASE 10 * 2  
  7. WHEN 20 THEN '20 correct'  
  8. WHEN 30 THEN '30 correct'  
  9. WHEN 40 THEN '40 correct'  
  10. END AS SIMPLE_CASE_Result; 
 
Example 2
  1. SELECT BookNumber, orderDate,  
  2. CASE Status  
  3. WHEN "Done"  
  4. THEN "Product_Shipped"  
  5. WHEN "In Progress"  
  6. THEN "Product_Not_Shipped"  
  7. END AS ProductStatus  
  8. FROM mysql_caseexpressions.bookorder;  
  9.   
  10. SELECT BookNumber, orderDate,  
  11. CASE WHEN Status = "Done"  
  12. THEN "Product_Shipped"  
  13. WHEN Status = "In Progress"  
  14. THEN "Product_Not_Shipped"  
  15. END AS ProductStatus  
  16. FROM mysql_caseexpressions.bookorder; 
 

Resources 

 
Here are some useful related resources:

CONCLUSION

 
In this article, I have discussed the concept of CASE Expressions in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL. 
 
Thanks for reading this article.