MySQL CASE Expression
MySQL CASE expression is a part of the control flow function that provides us to write an if-else or if-then-else logic to a query. This expression can be used anywhere that uses a valid program or query, such as SELECT, WHERE, ORDER BY clause, etc.
The CASE expression validates various conditions and returns the result when the first condition is true. Once the condition is met, it stops traversing and gives the output. If it will not find any condition true, it executes the else block. When the else block is not found, it returns a NULL value. The main goal of MySQL CASE statement is to deal with multiple IF statements in the SELECT clause.
We can use the CASE statement in two ways, which are as follows:
1. Simple CASE statement:
The first method is to take a value and matches it with the given statement, as shown below.
It returns the result when the first compare_value comparison becomes true. Otherwise, it will return the else clause.
After the successful execution of the above command, we will see the following output.
2. Searched CASE statement:
The second method is to consider a search_condition in the WHEN clauses, and if it finds, return the result in the corresponding THEN clause. Otherwise, it will return the else clause. If else clause is not specified, it will return a NULL value.
The CASE expression returns the result depending on the context where it is used. For example:
MySQL version support
The CASE statement can support the following MySQL versions:
Let us create a table 'students' and perform the CASE statement on this table.
In the above table, we can see that the class column contains the short form of the student's department. That's why we are going to change the short form of the department with the full form. Now, execute the following query to do this operation.
After the successful execution of the above query, we will get the following output. Here, we can see that the department column contains full form instead of a short form.