MySQL CASE Statement

The CASE statement in MySQL adds conditional logic to your queries. It allows you to return different values depending on which condition is met, similar to an if-then-else structure in programming.

CASE Statement: Definition and Usage

The CASE statement evaluates conditions sequentially. When it finds a true condition, it returns the corresponding result and stops. If no conditions are true, it returns the value in the ELSE part (if provided); otherwise, it returns NULL.

Syntax


CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    WHEN conditionN THEN resultN
    ELSE result
END;

Parameter Values

Parameter	Description
`condition1, condition2, ... conditionN`	The conditions to evaluate. These are checked in order. At least one condition is required.
`result1, result2, ... resultN`	The values returned if the corresponding condition is true. At least one result is required.
`ELSE result` (Optional)	The value returned if none of the conditions are true.

Examples

Conditional Output Based on Quantity

This example adds a text description ('QuantityText') based on the value of the 'Quantity' column. (Assumes an 'OrderDetails' table with 'OrderID' and 'Quantity' columns.)

Syntax


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;

Output


OrderID | Quantity | Result
-----------------------------
(OrderID) | (Quantity) | (Corresponding text based on Quantity)

Conditional Ordering

This example orders customers by City, but if City is NULL, it orders by Country. (Assumes a 'Customers' table with 'CustomerName', 'City', and 'Country' columns.)

Syntax


SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

Output


CustomerName | City        | Country
------------------------------------
(Customers sorted by City, then Country for NULL cities)