In this tutorial, I am going to explain CASE expressions in MySQL with examples. This article will cover the following topics. Let’s see.
Introduction
Simple CASE Expression
Searched CASE Expression
Difference between SIMPLE and SEARCHED CASE Expressions
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
Simple CASE Statement
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.
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;
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;
Example 2
SELECT CASE WHEN 5 * 5 - 5 / 5 + 6 = 30 THEN "TRUE"
ELSE "FALSE"
END AS SEARCHED_CASE_Result;
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;
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;
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;
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;
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;
