CASE() Function in MySQL

CASE() function in 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.

My Personal Notes arrow_drop_up

Save