In this tutorial, we will study the MySQL CASE function. The exciting thing about it is that MySQL also offers the CASE statement with syntax almost identical to the CASE function, but these are used for different use cases. So, we will see both; function and statement, with simple and easy-to-understand examples.
Let’s start first with the CASE function, and then we will head towards the CASE statement.
MySQL CASE Function
MySQL CASE function is a part of flow control functions, and it is also called a CASE Operator. It is similar to the conditional statement IF-THEN-ELSE, which iterates through the given conditions and returns the specified true block as soon as the first condition is satisfied and terminates.
We can also say it inherits the syntax of the SWITCH statement and an IF-ELSE statement because we can have a parameter to compare with CASE exactly like the SWITCH statement. However, you can also choose not to set any compare parameter.
MySQL CASE Function Syntax
The syntax for the CASE function is:
CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...] [ELSE result] END;Code language: SQL (Structured Query Language) (sql)- CASE is function name/ operator name
- value is the specified value you want to compare
- WHEN is the keyword to check the condition
- THEN is a keyword that executes the next block if the WHEN condition is satisfied
- END specifies the end of the CASE block.
Depending on the problem statement, we can have as many WHEN-THEN blocks as you want.
Alternative, you can have the following syntax as well :
CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END;Code language: SQL (Structured Query Language) (sql)Here, we haven’t specified any value and compare_value; instead, we can write conditions like IF statement in WHEN block and the respective results in THEN block.
Ensure you end the CASE function with the END keyword; otherwise, you will face an error.
Examples of MySQL CASE Function
We will see two examples for the MySQL CASE function based on the syntaxes mentioned above. The examples will be pretty straightforward, giving you a glimpse of how the function works.
First, we will create a table with a name field to store the names, and we will then write a CASE function to display the number of characters in names in words instead of digits. Here is the code:
First, create a table and insert values in that:
CREATE TABLE names(name VARCHAR(100));
INSERT INTO NAMES VALUES ("Tejas"), ("Dipak"), ("John"),("Mistry");Code language: SQL (Structured Query Language) (sql)
Then we write a query that includes CASE expression to get characters length in the name.
SELECT name, CASE CHAR_LENGTH(name)
WHEN 4 THEN "four"
WHEN 5 THEN "five"
WHEN 6 THEN "six"
ELSE "> SIX"
END
AS char_length FROM names;Code language: SQL (Structured Query Language) (sql)
Here, we have used the CASE expression in a query with a CHAR_LENGTH() function, which will count the number of characters of each name. Then the WHEN block will compare given values with the recently calculated value from CHAR_LENGTH() function and returns the digit in word format.
CASE expression will execute for each record in the table and displays the result with the new column name alias ‘char_length.’
Now, let’s see another way to perform the same operations using the second syntax of the CASE function.
SELECT name, case
WHEN CHAR_LENGTH(name)=4 THEN "four"
WHEN CHAR_LENGTH(name)=5 THEN "five"
WHEN CHAR_LENGTH(name)=6 THEN "six"
ELSE "> six"
END
As char_length From names;Code language: SQL (Structured Query Language) (sql)
Here, we have written conditions to check the length of the name instead of using comparing values, unlike the previous example.
You might have gotten an idea of how you can use and utilize the CASE function with MySQL queries.
Now, we will see the CASE statement, which looks pretty similar to the CASE function but works differently. At the end of this guide, you will also know the difference between the CASE function and the CASE statement.
MySQL CASE statement
MySQL CASE statement is different from the CASE function.
As it is a statement, the CASE statement cannot be used inside the query because the query cannot contain statements. It does not evaluate to value and can be used on its own, unlike the CASE function, which must be part of another statement or query.
Note that the CASE statement ends with END CASE instead of only END, and it cannot have an ELSE NULL clause.
Confusing, right? Let’s see syntax first and continue with the example to understand it better.
MySQL CASE Statement Syntax
Like the CASE function, the CASE statement also has two syntaxes; with and without case_value.
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ...[ELSE statement_list] END CASE;Code language: SQL (Structured Query Language) (sql)Or
CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASECode language: SQL (Structured Query Language) (sql)In the first CASE statement syntax, when case_value matches with when_value, corresponding THEN clause executes, and statement ends. If no condition is satisfied, the ELSE clause executes.
In the second syntax, search_condition is an expression precisely like the IF condition. The corresponding THEN block executes if the search_condition is true and the statement ends. If all search_condition expressions are false, the ELSE block executes.
The statement_list in above syntax is a set of one or more SQL statements, and it should not be empty.
You can have as many as WHEN blocks possible in your CASE statement, but remember to end the statement with END CASE.
Because the CASE statement cannot be used in a query, it is more helpful with procedures. We will look at a simple example to create a stored procedure and use a CASE statement to perform some actions.
Examples of MySQL CASE Statement
Here is a simple example: we will create a stored procedure and then write a CASE statement to convert numbers into words. There would be static values and words instead of dynamic, so it would be simple and easy to explain.
DELIMITER //
CREATE PROCEDURE JD()
BEGIN
DECLARE var INT DEFAULT 1;
CASE var
WHEN 1 THEN SELECT "ONE";
WHEN 2 THEN SELECT "TWO";
ELSE SELECT "> TWO";
END CASE;
END //Code language: SQL (Structured Query Language) (sql)
CALL JD();Code language: SQL (Structured Query Language) (sql)
Here, we created a stored procedure with one variable of name ‘var’ and assigned a default value of 1. Then, the ‘var’ variable will be compared with digits in the WHEN block, and as soon as a match happens, the respective THEN block will be executed, and the CASE statement ends. In our case, ‘var’ is initialized with one, so the first WHEN block will be executed, and output will be displayed as “one.”
We can also implement this using dynamic values, but this example focuses on the basic functionality of the CASE statement.
Difference Between CASE Function and CASE Statement
You might have understood the fundamental difference between the CASE function and statement by analyzing the above examples. CASE function is also called operator as well as expression, don’t be confused!
The CASE function evaluates to a value when we give some condition. Whereas the CASE statement does not evaluate to a value, but it executes one or multiple statements on some condition.
The CASE function must be a part of an expression like a query or assignment. Whereas, CASE statement can’t be used inside a query because the query doesn’t contain any statement.
Conclusion
Here is the detailed guide about the CASE function and CASE statement, where we went through their syntaxes and examples. We also understood how both are different and can be used in different scenarios. If you think we have missed anything, let us know below.
References
MySQL official documentation of case statement.
MySQL official documentation of case function.
Stackoverflow thread on case function vs case statement.