MySQL CASE Function

The CASE function in MySQL is used to perform conditional logic in SQL queries. It allows you to execute a series of conditions and return a value when the first true condition is met, similar to an IF-THEN-ELSE structure in programming languages.

Syntax

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

Parameters

Parameter	Description
`condition1`, `condition2`, ...	Required. The conditions that will be evaluated in sequence.
`result1`, `result2`, ...	Required. The values returned when the corresponding condition is true.
`result`	Optional. The result returned if no conditions are true (in the `ELSE` clause). If there is no `ELSE` part and no conditions are true, `NULL` is returned.

Definition and Usage

The CASE statement goes through each condition in order. Once a condition is true, the corresponding result is returned, and the subsequent conditions are not evaluated.
If none of the conditions are true, the ELSE result is returned. If no ELSE part is provided, the function returns NULL.

Return Values

The CASE function returns the result associated with the first condition that evaluates to true.
If no conditions match and there is an ELSE clause, the function returns the ELSE result.
If there is no ELSE clause and no conditions are met, the function returns NULL.

Usage Examples

Example 1: Conditional Logic to Determine Quantity Description

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;

In this example:

If the Quantity is greater than 30, it returns "The quantity is greater than 30".
If the Quantity is exactly 30, it returns "The quantity is 30".
Otherwise, it returns "The quantity is under 30".

Example 2: Conditional Sorting with `CASE`

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