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:
The first method is to take a value and matches it with the given statement, as shown below.
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
It returns the result when the first compare_value comparison becomes true. Otherwise, it will return the else clause.
mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
After the successful execution of the above command, we will see the following output.
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.
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
mysql> SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
The CASE expression returns the result depending on the context where it is used. For example:
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.
SELECT studentid, firstname, CASE class WHEN 'CS' THEN 'Computer Science' WHEN 'EC' THEN 'Electronics and Communication' ELSE 'Electrical Engineering' END AS department from students;
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.