Does case work in mysql?

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

Technical Details

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.

Can we use CASE in MySQL?

Does MySQL ignore CASE?

Why CASE is used in MySQL?

Is MySQL case