Example
Go through conditions and return a value when the first condition is met:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN "The quantity is greater than 30"
WHEN Quantity = 30 THEN "The quantity is 30"
ELSE "The quantity is under 30"
END
FROM OrderDetails;
Try it Yourself »
Definition and Usage
The CASE statement goes through conditions and return a value when the first condition is met [like an IF-THEN-ELSE statement]. So, once a condition is true, it will stop reading and return the result.
If no conditions are true, it will return the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
Syntax
CASE
WHEN condition1 THEN
result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
Parameter Values
condition1, condition2, ...conditionN | Required. The conditions. These are evaluated in the same order as they are listed |
result1, result2, ...resultN | Required. The value to return once a condition is true |
Technical Details
From MySQL 4.0 |
More Examples
The following SQL will order the customers by City. However, if City is NULL, then order by Country:
Example
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
[CASE
WHEN City IS NULL THEN Country
ELSE City
END];
Try it Yourself »
13.6.5.1 CASE Statement
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
The CASE
statement for stored programs implements a complex conditional construct.
For the first syntax, case_value
is an expression. This value is compared to the when_value
expression in each WHEN
clause until one of them is equal. When an
equal when_value
is found, the corresponding THEN
clause statement_list
executes. If no when_value
is equal, the ELSE
clause statement_list
executes, if there is one.
This syntax cannot be used to test for equality with NULL
because NULL = NULL
is false. See Section 3.3.4.6, “Working with NULL Values”.
For the
second syntax, each WHEN
clause search_condition
expression is evaluated until one is true, at which point its corresponding THEN
clause statement_list
executes. If no search_condition
is equal, the ELSE
clause statement_list
executes, if there is one.
If no when_value
or search_condition
matches the value tested and the CASE
statement contains
no ELSE
clause, a Case not found for CASE statement error results.
Each statement_list
consists of one or more SQL statements; an empty statement_list
is not permitted.
To handle situations where no value is matched by any WHEN
clause, use an ELSE
containing an empty BEGIN ...
END
block, as shown in this example. [The
indentation used here in the ELSE
clause is for purposes of clarity only, and is not otherwise significant.]
DELIMITER |
CREATE PROCEDURE p[]
BEGIN
DECLARE v INT DEFAULT 1;
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
END;
END CASE;
END;
|
CASE[] functionin MySQL is used to find a value by passing over conditions whenever any condition satisfies the given statement otherwise it returns the statement in an else part. However, when a condition is satisfied it stops reading further and returns the output.
Features:
- This function returns the statement in the else part if none of the stated conditions are true.
- This function returns NULL if none of the stated conditions are true as well as there is no else part also.
- This function comes under Advanced Functions.
- This function accepts two parameters namely conditions and results.
Syntax:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
Parameter:
This method accepts two parameters as given below:
- condition1, condition2, …conditionN: Specified conditions which are determined in the order they are stated.
- result1, result2, …resultN: Specified output which is to be returned if the stated condition is satisfied.
Returns: It returns a value by passing over conditions whenever any condition satisfies the given statement otherwise it returns the statement in an else part. And if none of the conditions are satisfied as well as there is no else part then it returns NULL.
Example 1: Using the CASE[] function and getting the output.
CREATE TABLE float01001 [ user_id int NOT NULL AUTO_INCREMENT, float_val float, PRIMARY KEY[user_id] ]; INSERT float01001[float_val] VALUES [1.9]; INSERT float01001[float_val] VALUES [1.1]; INSERT float01001[float_val] VALUES [3.9]; INSERT float01001[float_val] VALUES [5.0]; INSERT float01001[float_val] VALUES [10.9]; SELECT float_val, CASE WHEN float_val > 5 THEN "The value is greater than 5" WHEN float_val = 5 THEN "The value is 5" ELSE "The value is under 5" END as float_txt FROM float01001;
Output:
float_val | float_txt ------------------------------------------- 1.9 | The value is under 5 ------------------------------------------- 1.1 | The value is under 5 ------------------------------------------- 3.9 | The value is under 5 ------------------------------------------- 5 | The value is 5 ------------------------------------------- 10.9 | The value is greater than 5
Example 2:
Using the CASE[] function and checking if the length of the stated float value is greater than, or less than, or equal to 4.
CREATE TABLE float01001 [ user_id int NOT NULL AUTO_INCREMENT, float_val float, PRIMARY KEY[user_id] ]; INSERT float01001[float_val] VALUES [9.0]; INSERT float01001[float_val] VALUES [7.7]; INSERT float01001[float_val] VALUES [30.91]; INSERT float01001[float_val] VALUES [8.0]; INSERT float01001[float_val] VALUES [10.9]; SELECT float_val, CASE WHEN LENGTH[float_val] > 4 THEN "The length is greater than 4" WHEN LENGTH[float_val] = 4 THEN "The length is 4" ELSE "The length is less than 4" END as float_txt FROM float01001;
Output:
float_val | float_txt ------------------------------------------- 9 | The length is less than 4 ------------------------------------------- 7.7 | The length is less than 4 ------------------------------------------- 30.91 | The length is greater than 4 ------------------------------------------- 8.0 | The length is less than 4 ------------------------------------------- 10.9 | The length is 4
Example 3:
Using the CASE[] function and checking if the MRP of the stated item is greater than 400 or not.
CREATE TABLE package099 [ user_id int NOT NULL AUTO_INCREMENT, item VARCHAR[10], mrp int, PRIMARY KEY[user_id] ]; INSERT package099[item, mrp] VALUES ['book1', 350]; INSERT package099[item, mrp] VALUES ['book2', 500]; SELECT mrp, CASE WHEN mrp > 400 THEN "Buy this item" ELSE "Don't buy this item" END as txt FROM package099;
Output:
mrp | txt ------------------------------ 350 | Don't buy this item ------------------------------ 500 | Buy this item
Example 4:
Using CASE[] function and checking profit or loss.
CREATE TABLE package72 [ user_id int NOT NULL AUTO_INCREMENT, item VARCHAR[10], mrp int, sp int, PRIMARY KEY[user_id] ]; INSERT package72[item, mrp, sp] VALUES ['book1', 250, 255]; INSERT package72[item, mrp, sp] VALUES ['book2', 350, 370]; INSERT package72[item, mrp, sp] VALUES ['book3', 400, 350]; SELECT mrp,sp, CASE WHEN sp > mrp THEN "Profit" ELSE "Loss" END as PL FROM package72;
Output:
mrp | sp | PL ----------------------- 250 | 255 | Profit ----------------------- 350 | 370 | Profit ---------------------- 400 | 350 | Loss
Application: This function is used to find a value by passing over conditions whenever any condition satisfies the given statement otherwise it returns the statement in an else part.