In MySQL, the CASE statement is a powerful conditional expression that allows you to perform conditional logic within SQL queries. It evaluates a list of conditions and returns one result based on the first condition that is true, similar to the switch or if-else statements in other programming languages.
Here's the basic syntax of the CASE statement:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
- condition1, condition2, etc.: These are the conditions that are evaluated. If a condition evaluates to true, the corresponding `result` is returned.
- result1, result2, etc.: These are the values returned when the corresponding condition is true.
- default_result: This is an optional value returned if none of the conditions are true.
Example:
Suppose we have a table named employees with columns employee_id, first_name, last_name, and salary. We want to categorize employees based on their salary into three categories: "Low", "Medium", and "High".
SELECT
first_name,
last_name,
salary,
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary >= 50000 AND salary < 100000 THEN 'Medium'
ELSE 'High'
END AS salary_category
FROM
employees;
Output:
| first_name | last_name | salary | salary_category |
|------------|-----------|--------|-----------------|
| John | Doe | 45000 | Low |
| Jane | Smith | 75000 | Medium |
| Alice | Johnson | 110000 | High |
In this example, the CASE statement categorizes employees based on their salary into "Low", "Medium", or "High" categories.
The CASE statement in MySQL allows you to perform conditional logic within SQL queries. Here's an example of how to use the CASE statement with output:
Let's say we have a table named students with columns id, name, and score. We want to categorize students based on their scores into three groups: "Excellent" for scores above 90, "Good" for scores between 70 and 90 (inclusive), and "Needs Improvement" for scores below 70.
SELECT
name,
score,
CASE
WHEN score > 90 THEN 'Excellent'
WHEN score BETWEEN 70 AND 90 THEN 'Good'
ELSE 'Needs Improvement'
END AS score_category
FROM students;
This query will categorize each student's score into one of the specified categories based on the defined conditions and output the student's name, score, and the corresponding category.
Example Output:
| name | score | score_category |
| ------- | ----- | ----------------- |
| Alice | 95 | Excellent |
| Bob | 80 | Good |
| Charlie | 65 | Needs Improvement|
| David | 75 | Good |
In this output, Alice's score is above 90, so she falls into the "Excellent" category. Bob's score falls between 70 and 90, placing him in the "Good" category. Charlie's score is below 70, so he falls into the "Needs Improvement" category. David's score also falls between 70 and 90, so he is categorized as "Good" as well.
Here are five frequently asked questions (FAQs) about the `CASE` statement in SQL along with brief answers:
1. What is the CASE statement in SQL?
The CASE statement in SQL is a conditional expression that allows you to perform conditional logic within a query. It evaluates a list of conditions and returns one result based on the first condition that is true.
2. How is the CASE statement structured?
The basic structure of the CASE statement in SQL is:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
It evaluates each condition in order and returns the corresponding result for the first condition that is true. If no condition is true, it returns the default result (if specified).
3. Can I use the CASE statement with aggregate functions?
Yes, the CASE statement can be used with aggregate functions like SUM, COUNT, AVG, etc., allowing you to perform conditional aggregation based on certain criteria.
4. Can I nest CASE statements in SQL?
Yes, you can nest CASE statements within each other to handle more complex conditional logic. This allows for greater flexibility in defining multiple levels of conditions and results.
5. In which SQL clauses can I use the CASE statement?
You can use the CASE statement in various clauses of a SQL query, including SELECT, WHERE, ORDER BY, GROUP BY, and HAVING, allowing you to conditionally manipulate data and control the output of your queries.
