THE WORLD'S LARGEST WEB DEVELOPER SITE

MySQL CASE Function

❮ MySQL Functions

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 something else"
END
FROM OrderDetails;
Try it Yourself »

Definition and Usage

The CASE function 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, this function will stop and return the result (and not go through the rest).

If none of the specified conditions evaluates to true, this function returns the value in the ELSE clause.

If there is no ELSE clause and no conditions evaluates to true, this function returns NULL.

Syntax

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

Parameter Values

Parameter Description
expression Optional. The value to compare to the list of conditions
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

Works in: From MySQL 4.0

More Examples

Example

Go through conditions and return a value when the first condition is met:

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

❮ MySQL Functions