MySQL CASE Statement

Introduction to MySQL CASE Statement

MySQL CASE is a MySQL Statement query keyword that defines the way to handle the loop concepts to execute the set of conditions and return the match case using IF ELSE. CASE in MySQL is a type of control statement which validates the set of conditional cases and displays the value when the first case is meeting otherwise else value and exits the loop. If none cases are found TRUE and the statement does not have ELSE part or value, then the CASE return NULL. Basically, the CASE statement is just like similar to IF THEN ELSE logical loop statements. When run on MySQL server it reads the conditional expressions and when the expression case is matched, and the nit shows the result. After that it stops execution further.

Syntax of MySQL CASE Statement

The syntax below defines the CASE statement SQL query structure:

CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END;

Let us discuss these parameters given in the syntax above:

• Cond1,cond2,…..,condN: Denotes the required conditions to be evaluated in the list of CASE statements.
• Value1, value2, …..,valueN: Represents the respective values required to display when a condition is met.
• Value: Denotes the value to be displayed when else part is meeting.

This part allows adding logical CASE statements in a query in MySQL. To make a valid statement we use the CASE statement anywhere with the clauses such as WHERE SELECT and ORDER BY. You can evaluate through the syntax shown below:

SELECT column1,column2,
CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END
FROM TableName;

How Does CASE Statement Work in MySQL?

The CASE Statement contains two procedures: one is Simple CASE and the other one is Searched CASE. We use a CASE statement to provide the result values based on a matched condition using the logical control method to the SQL queries together with the SQL clauses like SELECT, WHERE and ORDER BY.

Suppose, the following query explains the CASE logical part where we have mentioned a table like Students with fields StudentName, State, City, then the query will be written as:

SELECT StudentName, State, City FROM Students ORDER BY (
CASE
WHEN State IS NULL THEN City
ELSE State
END);

From here we will come to know that when we have applied the CASE statement in the SELECT query to fetch the particular value that satisfies a specific case condition. In the above illustration we have put the CASE statement on columns State and City where if the State column contains a NULL value then the query displays City column value and if case statement is not valid or if value not found then, else part value i.e. State column value is returned. Here, we have fetched the names of students, state, and city where the CASE is applied with ORDER BY clause also to sort the result rows.

In the Simple CASE, the CASE the column value is matched with the conditional statement value in the WHEN clauses for equivalence and then, produces the result value after than one in the syntax. But if there is no value that is equal then, it returns the ELSE part value if provided.

You should not use NULL in the WHEN clause value because if executed the logical part will be NULL = NULL which is FALSE as a result.

Now for Search CASE, it follows the same logical procedure as such Simple CASE but one part of the search case makes it different to perform. Here, the CASE on satisfying results the value whose MySQL Data Type is based on the context it is used for. For example, if there is a character string context used in the statement then, the result value will be in the same data type, string. Also, if the CASE conditional expression uses a numeric context then, the value returned will be either in integer, decimal or real value data type.

Examples to Implement MySQL CASE Statement

Following are the CASE statement examples with outputs:

Example #1 – CASE Statement with SELECT & ORDER BY Clauses

Let us execute the above SQL query and show the result on the CASE part execution. For this, we need to create a table Students and insert some values also.

1. Creating a Table

CREATE TABLE Students(StudentIDint, StudentNamevarchar(255), State varchar(255), City varchar(255) );

2. Inserting Few Fields

INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('01', 'Vedika', 'UP', 'Kanpur');

And so on.

3. SELECT Statement to Display the Table Data

Query:

SELECT * FROM Students;

Output:

4. CASE Statement with SELECT Query

Query:

SELECT StudentName, State, City FROM Students ORDER BY (
CASE
WHEN State IS NULL THEN City
ELSE State
END);

Output:

Example #2 – CASE Statement with Aggregate Function

We will calculate the total sales count from the Orders table by order status using the CASE statement together with the SUM() MySQL function.

1. Creating a Table

 Query:

CREATE TABLE Orders (OrderIDint,SalesIDint, OrderStatusvarchar(255) );

2. Inserting a Few Fields

 Query:

INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('10', '001', 'Shipped');

And so on.

3. SELECT statement to display the Table Data

 Query:

SELECT * FROM Orders;

Output:

3. CASE Statement with SUM() and COUNT as Aggregate Functions in the SQL SELECT Query

 Query:

SELECT
SUM(CASE
WHEN OrderStatus = 'Success' THEN 1
ELSE 0
END) AS 'Success Count',
SUM(CASE
WHEN OrderStatus = 'On Hold' THEN 1
ELSE 0
END) AS 'Hold Count',
SUM(CASE
WHEN OrderStatus = 'In Process' THEN 1
ELSE 0
END) AS 'Processing',
SUM(CASE
WHEN OrderStatus = 'Shipped' THEN 1
ELSE 0
END) AS 'Shipping count',
SUM(CASE
WHEN OrderStatus = 'Cancelled' THEN 1
ELSE 0
END) AS 'Cancellation Count',
COUNT(*) AS Sum Total
FROM
Orders;

Output:

Explanation: In the query above, firstly the CASE statements execute and return 1 when the case is matched with the respective Order Status such as Success, In Progress, Shipped, Cancelled, On Hold, and otherwise zero. Secondly, after this, the Sum() function works to find out the total number of orders according to the order status.

Conclusion

• MySQL CASE statement permits to execute the IF ELSE logic to the SQL queries to examine the conditional statements and fetch the required result sets or values from the database tables.
• We can use the CASE statement with stored procedures, stored events, functions, and triggers only. It is used to compare the conditional expression to a range of distinct values which provides the corresponding result that holds the data type that depends on the context used in the query.
• Hence, we can say that the CASE statement in MySQL makes the query code more efficient and readable.

Recommended Articles

This is a guide to MySQL CASE Statement. Here we discuss the Introduction of MySQL CASE Statement and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

1. Introduction to MySQL Operators
2. Top 23 MySQL String functions
3. MySQL vs SQLite | Top 14 Comparisons
4. Guide to MySQL Timestamp