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; 

Example 2

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

Example 3

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

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; 

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;