MySQL CASE operator
CASE operator
In MySQL, the CASE statement is used to apply a complex conditional construct in a stored program.
This function is useful in -
- It allows you to implement conditional logic directly within a query.
- This is invaluable for handling different scenarios and producing different results based on specified conditions.
- CASE can be used to transform data on-the-fly. For example, you can use it to categorize or label data based on specific criteria.
- You can use CASE to dynamically select different columns based on specific conditions.
- CASE allows you to set up contingency plans in case certain conditions are not met.
- It's crucial in generating customized reports where you want to format or label data differently based on certain criteria.
- By using CASE to streamline data processing, you can sometimes avoid the need for more complex joins or subqueries, which can lead to more efficient queries.
Syntax:
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
OR
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
- The first syntax returns the result where value=compare_value.
- The second syntax returns the result for the first condition that is true.
- The list of corresponding SQL statements will execute when a search condition evaluates to true.
- The statement list in ELSE part will execute when no search condition matches.
- If there is no matching value found in the ELSE part, NULL will be returned.
- Each statement list can contain one or more statements and no empty statement list is allowed.
MySQL Version: 8.0
The return type of a CASE expression is the compatible aggregated type of all return values and depends on the context in which it is used. If used in a numeric context, the result is returned as a decimal, real, or integer value. If used in a string context, the result is returned as a string.
Note: The syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement, CASE Syntax, for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.
Example: MySQL CASE operator
In the following statement, CASE is 1, therefore "this is case one" is returned.
Code:
-- This SQL statement uses a CASE expression to return a specific string based on the value of an expression
-- Explanation: The query evaluates the value 1 and matches it against the specified cases, returning the corresponding result.
SELECT
CASE 1 -- The CASE expression begins, and the value to be compared (1) is specified
WHEN 1 THEN 'this is case one' -- If the value is 1, return the string 'this is case one'
WHEN 2 THEN 'this is case two' -- If the value is 2, return the string 'this is case two'
ELSE 'this is not in the case' -- If the value does not match any WHEN condition, return the string 'this is not in the case'
END AS 'how to execute case statement'; -- The END keyword marks the end of the CASE expression and assigns an alias to the result
Explanation:
- The SELECT statement is used to evaluate expressions and return the result.
- The CASE expression allows for conditional logic to return specific results based on the value of an expression.
- In this specific CASE expression:
- CASE 1: The expression to be evaluated is 1.
- WHEN 1 THEN 'this is case one': If the expression is 1, return the string 'this is case one'.
- WHEN 2 THEN 'this is case two': If the expression is 2, return the string 'this is case two'.
- ELSE 'this is not in the case': If the expression does not match any WHEN condition, return the string 'this is not in the case'.
- END AS 'how to execute case statement': The END keyword marks the end of the CASE expression, and the result is given the alias 'how to execute case statement'.
- Since the expression 1 matches the first WHEN condition, the result of this query is 'this is case one'.
Output:
mysql> SELECT CASE 1 WHEN 1 THEN 'this is case one'
-> WHEN 2 THEN 'this is case two'
-> ELSE 'this is not in the case'
-> END as 'how to execute case statement';
+-------------------------------+
| how to execute case statement |
+-------------------------------+
| this is case one |
+-------------------------------+
1 row in set (0.00 sec)
Example: MySQL CASE operator when not matching
In the following statement CASE is 4, therefore it returns the ELSE part, i.e. 'this is not in the case'.
Code:
-- This SQL statement uses a CASE expression to return a specific string based on the value of an expression
-- Explanation: The query evaluates the value 4 and matches it against the specified cases, returning the corresponding result.
SELECT
CASE 4 -- The CASE expression begins, and the value to be compared (4) is specified
WHEN 1 THEN 'this is case one' -- If the value is 1, return the string 'this is case one'
WHEN 2 THEN 'this is case two' -- If the value is 2, return the string 'this is case two'
ELSE 'this is not in the case' -- If the value does not match any WHEN condition, return the string 'this is not in the case'
END AS 'how to execute case statement'; -- The END keyword marks the end of the CASE expression and assigns an alias to the result
Explanation:
- The SELECT statement is used to evaluate expressions and return the result.
- The CASE expression allows for conditional logic to return specific results based on the value of an expression.
- In this specific CASE expression:
- CASE 4: The expression to be evaluated is 4.
- WHEN 1 THEN 'this is case one': If the expression is 1, return the string 'this is case one'.
- WHEN 2 THEN 'this is case two': If the expression is 2, return the string 'this is case two'.
- ELSE 'this is not in the case': If the expression does not match any WHEN condition, return the string 'this is not in the case'.
- END AS 'how to execute case statement': The END keyword marks the end of the CASE expression, and the result is given the alias 'how to execute case statement'.
- Since the expression 4 does not match any of the WHEN conditions, the result of this query is 'this is not in the case'.
Output:
mysql> SELECT CASE 4 WHEN 1 THEN 'this is case one'
-> WHEN 2 THEN 'this is case two'
-> ELSE 'this is not in the case'
-> END as 'how to execute case statement';
+-------------------------------+
| how to execute case statement |
+-------------------------------+
| this is not in the case |
+-------------------------------+
1 row in set (0.00 sec)
Example : MySQL CASE operator when matching
In the following statement, CASE is 2, therefore it returns 'this is case two'.
Code:
-- This SQL statement uses a CASE expression to return a specific string based on the value of an expression
-- Explanation: The query evaluates the value 2 and matches it against the specified cases, returning the corresponding result.
SELECT
CASE 2 -- The CASE expression begins, and the value to be compared (2) is specified
WHEN 1 THEN 'this is case one' -- If the value is 1, return the string 'this is case one'
WHEN 2 THEN 'this is case two' -- If the value is 2, return the string 'this is case two'
ELSE 'this is not in the case' -- If the value does not match any WHEN condition, return the string 'this is not in the case'
END AS 'how to execute case statement'; -- The END keyword marks the end of the CASE expression and assigns an alias to the result
Explanation:
- The SELECT statement is used to evaluate expressions and return the result.
- The CASE expression allows for conditional logic to return specific results based on the value of an expression.
- In this specific CASE expression:
- CASE 2: The expression to be evaluated is 2.
- WHEN 1 THEN 'this is case one': If the expression is 1, return the string 'this is case one'.
- WHEN 2 THEN 'this is case two': If the expression is 2, return the string 'this is case two'.
- ELSE 'this is not in the case': If the expression does not match any WHEN condition, return the string 'this is not in the case'.
- END AS 'how to execute case statement': The END keyword marks the end of the CASE expression, and the result is given the alias 'how to execute case statement'.
- Since the expression 2 matches the second WHEN condition, the result of this query is 'this is case two'.
Output:
mysql> SELECT CASE 2 WHEN 1 THEN 'this is case one'
-> WHEN 2 THEN 'this is case two'
-> ELSE 'this is not in the case'
-> END as 'how to execute case statement';
+-------------------------------+
| how to execute case statement |
+-------------------------------+
| this is case two |
+-------------------------------+
1 row in set (0.00 sec)
Example : MySQL CASE operator using greater than operator
In the following example, as the condition 2>3 is FALSE, it returns the ELSE part, i.e. 'this is false'.
Code:
-- This SQL statement uses a CASE expression to evaluate a condition and return a specific string based on the result
-- Explanation: The query evaluates the condition '2 > 3' and returns the corresponding result based on whether the condition is true or false.
SELECT
CASE -- The CASE expression begins
WHEN 2 > 3 THEN 'this is true' -- If the condition '2 > 3' is true, return the string 'this is true'
ELSE 'this is false' -- If the condition is not true, return the string 'this is false'
END; -- The END keyword marks the end of the CASE expression
Explanation:
- The SELECT statement is used to evaluate expressions and return the result.
- The CASE expression allows for conditional logic to return specific results based on the evaluation of a condition.
- In this specific CASE expression:
- CASE: The CASE expression begins without specifying a value to compare directly, indicating it will evaluate a condition.
- WHEN 2 > 3 THEN 'this is true': The condition 2 > 3 is evaluated. If it is true, the result will be 'this is true'.
- ELSE 'this is false': If the condition 2 > 3 is not true, the result will be 'this is false'.
- END: The END keyword marks the end of the CASE expression.
- Since the condition 2 > 3 is false, the result of this query is 'this is false'.
Output:
mysql> SELECT CASE WHEN 2>3 THEN 'this is true' ELSE 'this is false' END; +-------------------------------------------------------------+ | CASE WHEN 2>3 THEN 'this is true' ELSE 'this is false' END | +-------------------------------------------------------------+ | this is false | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
Example : MySQL CASE operator with less than operator
In the following example as the condition 2<3 is TRUE, therefore it returns 'this is true'.
Code:
-- This SQL statement uses a CASE expression to evaluate a condition and return a specific string based on the result
-- Explanation: The query evaluates the condition '2 < 3' and returns the corresponding result based on whether the condition is true or false.
SELECT
CASE -- The CASE expression begins
WHEN 2 < 3 THEN 'this is true' -- If the condition '2 < 3' is true, return the string 'this is true'
ELSE 'this is false' -- If the condition is not true, return the string 'this is false'
END; -- The END keyword marks the end of the CASE expression
Explanation:
- The SELECT statement is used to evaluate expressions and return the result.
- The CASE expression allows for conditional logic to return specific results based on the evaluation of a condition.
- In this specific CASE expression:
- CASE: The CASE expression begins without specifying a value to compare directly, indicating it will evaluate a condition.
- WHEN 2 < 3 THEN 'this is true': The condition 2 < 3 is evaluated. If it is true, the result will be 'this is true'.
- ELSE 'this is false': If the condition 2 < 3 is not true, the result will be 'this is false'.
- END: The END keyword marks the end of the CASE expression.
- Since the condition 2 < 3 is true, the result of this query is 'this is true'.
Output:
mysql> SELECT CASE WHEN 2< 3 THEN 'this is true'ELSE 'this is false' END; +-------------------------------------------------------------+ | CASE WHEN 2< 3 THEN 'this is true'ELSE 'this is false' END | +-------------------------------------------------------------+ | this is true | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
Example : MySQL CASE operator with no matching
In the following example, CASE is not satisfied by either of the WHEN, it returns NULL.
Code:
-- This SQL statement uses a CASE expression to perform a binary comparison and return a specific result based on the value
-- Explanation: The query evaluates the binary value of 'A' and matches it against the specified cases, returning the corresponding result.
SELECT
CASE BINARY 'A' -- The CASE expression begins, and the BINARY keyword forces a binary comparison for the value 'A'
WHEN 'a' THEN 1 -- If the binary value of 'A' matches 'a', return 1
WHEN 'b' THEN 2 -- If the binary value of 'A' matches 'b', return 2
END; -- The END keyword marks the end of the CASE expression
Explanation:
- The SELECT statement is used to evaluate expressions and return the result.
- The CASE expression allows for conditional logic to return specific results based on the evaluation of an expression.
- In this specific CASE expression:
- CASE BINARY 'A': The value to be compared is 'A', and the BINARY keyword forces a binary comparison, which makes the comparison case-sensitive.
- WHEN 'a' THEN 1: If the binary value of 'A' matches 'a', return 1. In a binary comparison, 'A' does not match 'a' because of case sensitivity.
- WHEN 'b' THEN 2: If the binary value of 'A' matches 'b', return 2. In a binary comparison, 'A' does not match 'b' because the characters are different.
- END: The END keyword marks the end of the CASE expression.
- Since the binary value of 'A' does not match either 'a' or 'b', the result of this query is NULL. The CASE expression does not have an ELSE clause, so if no conditions are met, it returns NULL.
Output:
mysql> SELECT CASE BINARY 'A' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; +-----------------------------------------------------+ | CASE BINARY 'A' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END | +-----------------------------------------------------+ | NULL | +-----------------------------------------------------+ 1 row in set (0.00 sec)
Previous: XOR operator
Next: IF()
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics