Mysql - CASE Function
The `CASE` function in MySQL is used to implement conditional logic in SQL queries. It allows you to execute a series of conditions and return a specific result when a condition is met. This can be particularly useful for transforming data in your SELECT queries based on certain criteria.
The `CASE` function has two formats:
- Simple CASE Function
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END
- Searched CASE Function
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
Let's consider an `employees` table:
| id | name | department | salary |
|---|---|---|---|
| 1 | Jane Smith | IT | 500000 |
| 2 | John Doe | HR | 350000 |
| 3 | Johny Smith | IT | 50000 |
| 4 | Jeffer Joe | Finance | 380000 |
Example of the Simple CASE Function
To categorize employees by department,
SELECT name, department,
CASE department
WHEN 'IT' THEN 'Technical Department'
WHEN 'HR' THEN 'Human Resources'
WHEN 'Finance' THEN 'Financial Department'
ELSE 'Other Department'
END AS department_category
FROM employees;
In this query,
- Checks the value of the `department` column.
- Returns a specific string based on the department's value.
- Provides a default value with `ELSE` if none of the `WHEN` conditions match.
Output of the following -
| name | department | department_category |
|---|---|---|
| Jane Smith | IT | Technical Department |
| John Doe | HR | Human Resources |
| Johny Smith | IT | Technical Department |
| Jeffer Joe | Finance | Financial Department |
Example of the Searched CASE Function
To categorize employees based on salary ranges, you could use a searched CASE function:
SELECT name, department, salary,
CASE
WHEN salary >= 75000 THEN 'High'
WHEN salary BETWEEN 50000 AND 74999 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;
In this query,
- Evaluates multiple conditions.
- Returns a result based on which condition is true.
- The `ELSE` clause provides a default result if none of the conditions are met.
Output of the following -
| name | department | salary | salary_category |
|---|---|---|---|
| Jane Smith | IT | 500000 | High |
| John Doe | HR | 350000 | High |
| Johny Smith | IT | 50000 | Medium |
| Jeffer Joe | Finance | 380000 | High |
Benefits of Using the `CASE` Function
- Flexibility: Allows you to handle complex conditional logic directly within your SQL queries.
- Readability: Makes your SQL code easier to understand by clearly defining different cases and their corresponding results.
- Efficiency: Reduces the need for multiple separate queries or complex application logic to handle conditional data transformations.
Terms of Use About Us Privacy Policy
All Rights Reserved.