CASE

Example

Evaluate the conditions sequentially and return a value as soon as the first condition is satisfied.

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;

Definition and Usage

The CASE statement evaluates conditions and returns a value when the first condition is satisfied, similar to an IF-THEN-ELSE statement. Once a condition is met, it stops evaluating further conditions and returns the corresponding result.

If none of the conditions are met, it returns the value specified in the ELSE clause.

If there is no ELSE clause and no conditions are met, 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	Required: The conditions, evaluated in the order they are listed.
result1, result2, …resultN	Required: The value to return when a condition evaluates to true.

Technical Details

Works in:

From MySQL version 4.0

More Examples

The following SQL statement will sort customers by their city. If the city is NULL for any customer, it will then sort by the country instead.

Example

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