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.

Syntax

snippet

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.

Example

snippet

mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

Output

After the successful execution of the above command, we will see the following output.

MySQL CASE Expression

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.

Syntax

snippet

CASE 
	WHEN [condition] THEN result 
	[WHEN [condition] THEN result ...] 
	[ELSE result] 
END

Example

snippet

mysql> SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;

Output

MySQL CASE Expression

Return Type

The CASE expression returns the result depending on the context where it is used. For example:

If it is used in the string context, it returns the string result.
If it is used in a numeric context, it returns the integer, float, decimal value.

MySQL version support

The CASE statement can support the following MySQL versions:

MySQL 8.0
MySQL 5.7
MySQL 5.6
MySQL 5.5
MySQL 5.1
MySQL 5.0
MySQL 4.1
MySQL 4.0
MySQL 3.23.3

Let us create a table 'students' and perform the CASE statement on this table.

MySQL CASE Expression

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.

snippet

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.

MySQL CASE Expression