CASE
SQL construct
Checks the WHEN branches in order and returns the result of the first matching one.
MySQL 8.1
CASE [expression] WHEN condition THEN result [WHEN ...] [ELSE result] ENDParameters
- expressionoptional
- Expression compared with the values in the WHEN branches
- conditionBOOLEAN
- Condition of the branch, or the value to compare with expression if it is given
- result
- Value the branch returns when its condition is true
- ELSE resultoptional
- Value returned when no branch matches
Return value
The result of the first matching branch; NULL if no branch matches and there is no ELSE
Examples
Comparing two numbers
MySQL 8.1
SELECT CASE
WHEN 10 > 5 THEN 'greater'
ELSE 'less or equal'
END AS result;Family role by status
MySQL 8.1
SELECT member_name,
CASE
WHEN status IN ('father', 'mother') THEN 'parent'
ELSE 'child'
END AS role
FROM FamilyMembers;Simple form without ELSE
MySQL 8.1
SELECT member_name,
CASE
status
WHEN 'father' THEN 'dad'
WHEN 'mother' THEN 'mom'
END AS parent_role
FROM FamilyMembers;Details
CASE has two forms. Without expression, each WHEN has its own condition. With expression, it is compared with the value of each branch using =, so a WHEN NULL branch never matches: to check for NULL, use the form without expression and the condition WHEN expression IS NULL.