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

Most Read