Post
Ask Question

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 the 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 various examples. Let's see.

CREATE DATABASE MySQL_CASEExpressions;  
  USE MySQL_CASEExpressions;  
  CREATE TABLE Bookorder(  
  BookNumber INT NOT NULL,  
  orderDate datetime NOT NULL,  
  shippedDate DATE DEFAULT NULL,  
  Status VARCHAR(50),  
  OrderAcknowledgeDate datetime,  
  PRIMARY KEY(BookNumber)  
); 

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

SELECT CASE 10 * 2 WHEN 25 THEN 'Incorrect'  
WHEN 40 THEN '40 Incorrect'  
ELSE 20 END AS CASE_Result; 

simplecasexprression

Example 2

SELECT CASE 5 * 5 - 5 / 5 + 6 WHEN 30 THEN "TRUE"  
ELSE "FALSE"  
END AS "Results"; 

selectcase2

Example 3

SELECT CASE 10 * 2  
WHEN 20 THEN '20 correct'  
WHEN 30 THEN '30 correct'  
WHEN 40 THEN '40 correct'  
END AS Result; 

resultgrid

Example 4

SELECT BookNumber, orderDate,  
CASE Status  
WHEN "Done"  
THEN "Product_Shipped"  
WHEN "In Progress"  
THEN "Product_Not_Shipped"  
END AS ProductStatus  
FROM mysql_caseexpressions.bookorder; 

orderdtae

Example 5. With Aggregate Function

SELECT SUM(CASE Status WHEN 'Done'  
    THEN 1 ELSE 0 END) AS 'Product_Shipped',  
  SUM(CASE Status WHEN 'In Progress'  
    THEN 1 ELSE 0 END) AS 'Product_Not_Shipped',  
  COUNT( * ) AS TotalOrders  
FROM Bookorder; 

shippedfunction

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

SELECT CASE WHEN 10 * 2 = 25 THEN 'Incorrect'  
WHEN 10 * 2 = 40 THEN '40 Incorrect'  
ELSE "Should be 10*2=20"  
END AS SEARCHED_CASE_Result; 

sqlfunction

Example 2

SELECT CASE WHEN 5 * 5 - 5 / 5 + 6 = 30 THEN "TRUE"  
ELSE "FALSE"  
END AS SEARCHED_CASE_Result; 

selectinmysql

Example 3

SELECT CASE WHEN 10 * 2 = 20 THEN '20 correct'  
WHEN 10 * 2 = 30 THEN '30 correct'  
WHEN 10 * 2 = 40 THEN '40 correct'  
END AS SEARCHED_CASE_Result; 

searchcaseresult

Example 4

SELECT BookNumber, orderDate,  
CASE WHEN Status = "Done"  
THEN "Product_Shipped"  
WHEN Status = "In Progress"  
THEN "Product_Not_Shipped"  
END AS ProductStatus  
FROM mysql_caseexpressions.bookorder; 

orderdate

Example 5. With Aggregate Function

SELECT SUM(CASE WHEN Status = 'Done'  
    THEN 1 ELSE 0 END) AS 'Product_Shipped',  
  SUM(CASE WHEN Status = 'In Progress'  
    THEN 1 ELSE 0 END) AS 'Product_Not_Shipped',  
  COUNT( * ) AS TotalOrders  
FROM Bookorder; 

aggregatefunction

Difference between SIMPLE and SEARCHED CASE Expressions

Example 1

SELECT CASE WHEN 10 * 2 = 20 THEN '20 correct'  
WHEN 10 * 2 = 30 THEN '30 correct'  
WHEN 10 * 2 = 40 THEN '40 correct'  
END AS SEARCHED_CASE_Result,  
  CASE 10 * 2  
WHEN 20 THEN '20 correct'  
WHEN 30 THEN '30 correct'  
WHEN 40 THEN '40 correct'  
END AS SIMPLE_CASE_Result; 

caseresult

Example 2

SELECT BookNumber, orderDate,  
CASE Status  
WHEN "Done"  THEN "Product_Shipped" 
 WHEN "In Progress"  THEN "Product_Not_Shipped"  END AS ProductStatus  
FROM mysql_caseexpressions.bookorder; 
 
  SELECT BookNumber, orderDate,  
CASE WHEN Status = "Done"  
THEN "Product_Shipped"  
WHEN Status = "In Progress"  
THEN "Product_Not_Shipped"  
END AS ProductStatus  
FROM mysql_caseexpressions.bookorder; 

productstatus

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.

Resources

Here are some useful related resources:


Similar Articles