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.

The syntax below defines the CASE statement SQL query structure:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

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;

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.

Following are the CASE statement examples with outputs:

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:

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.

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 –

Related Courses