mysql case when

The MySQL CASE statement is a conditional expression that evaluates a list of conditions and returns a specific result when the first condition is met. It works similarly to an if-then-else statement in programming.

Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE result
END;

• The CASE statement goes through conditions sequentially.
• It returns the value corresponding to the first true condition.
• If no conditions are true and there is an ELSE clause, it returns the ELSE result.
• If no conditions are true and there is no ELSE clause, it returns NULL.
• It can be used in various parts of SQL queries such as SELECT, WHERE, and ORDER BY.

Usage Example

Suppose you want to categorize employees based on their salary:

sql

SELECT employee_id, employee_name, salary,
  CASE
    WHEN salary > 100000 THEN 'High'
    WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_category
FROM employees;

This returns a category of 'High', 'Medium', or 'Low' based on the salary value.

Additional Notes

• CASE statement evaluation stops as soon as a true condition is found.
• It enhances query flexibility and allows implementing if-then-else logic within SQL queries.
• It supports both numeric and string comparisons.
• It can be combined with aggregation functions like SUM and COUNT for conditional aggregation in GROUP BY queries.

These points summarize the core use and syntax of the MySQL CASE statement.