SQL: CASE expression reference

In a nutshell

The MySQL CASE expression is used to evaluate conditions and return a result based on the result of those conditions. The CASE expression can take two forms: simple and searched.

1. Simple CASE expression

Example

CASE expression
   WHEN value1 THEN result1
   WHEN value2 THEN result2
   ...
   ELSE default_result
END

Example

SELECT id, name,
   CASE department
      WHEN 'IT' THEN 'Information Technology'
      WHEN 'HR' THEN 'Human Resources'
      WHEN 'FIN' THEN 'Finance'
      ELSE 'Unknown'
   END AS department_name
FROM employees;

2. Searched CASE expression

Syntax

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   ELSE default_result
END

Example

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 AS QuantityText
FROM OrderDetails;