MySQL CASE Statement
The CASE
statement checks for the conditions and returns a value. There can be multiple conditions. If the first condition is not satisfied, then it checks the next condition and so on.
If no conditions are true, it returns the value in the ELSE
clause.
If the ELSE
part is not available, then it returns NULL.
Syntax for CASE Statement
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
.
.
.
WHEN condition_N THEN result_N
ELSE result
END;
Assume the table employees
with the following records.
empno | name | age | role | location |
---|---|---|---|---|
001 | Andrew | 30 | Manager | India |
002 | Beslin | 28 | Business Analyst | India |
003 | Joanna | 23 | Senior Developer | USA |
004 | Rayan | 26 | Technical Lead | Canada |
Example for CASE Statement
select empno, name, role,
case
when location='India' THEN 'Local Job'
when location='USA' THEN 'Remote Job'
else 'Contract Job'
end as job_type
from employees;
Output
empno | name | role | job_type |
---|---|---|---|
001 | Andrew | Manager | Local Job |
002 | Beslin | Business Analyst | Local Job |
003 | Joanna | Senior Developer | Remote Job |
004 | Rayan | Technical Lead | Contract Job |